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

with zd as (
    select distinct(device_id) device_id
    from user_profile
    where university = '浙江大学'
),
qu_detail as (
    select question_practice_detail.device_id de_id,
        question_practice_detail.question_id que_id,
         question_practice_detail.result  result,
        question_detail.difficult_level difficult_level
    from zd
     join question_practice_detail  on zd.device_id=question_practice_detail.device_id
    left join question_detail on question_practice_detail.question_id = question_detail.question_id

)
select difficult_level, sum(if(qu_detail.result='right',1,0))/count(*) correct_rate
from qu_detail
group by difficult_level
order by correct_rate