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

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