题解 | #浙大不同难度题目的正确率#

select
difficult_level
,sum(case when result='right' then 1 else 0 end )/count(question_id) as correct_rate
from(
    select
    a.device_id
    ,university
    ,b.question_id
    ,result
    ,difficult_level
    from(
        select
        device_id
        ,university
        from user_profile
        where university='浙江大学'
    )as a
    inner join (
        select
        device_id
        ,question_id
        ,result
        from question_practice_detail
    )as b
    on a.device_id=b.device_id
   inner join(
        select 
        question_id
        ,difficult_level
        from question_detail
    )as c
    on b.question_id=c.question_id
) as d
group by difficult_level
order by correct_rate