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

with
    t1 as (
        select
            u.device_id,
            q.question_id,
            q.result
        from
            user_profile as u
            right join question_practice_detail as q on u.device_id = q.device_id
        where
            university = '浙江大学'
        order by
            u.device_id,
            question_id
    )
select
    qd.difficult_level,
    sum(if (result = 'right', 1, 0)) / count(1) as correct_rate
from
    t1
    left join question_detail as qd on t1.question_id = qd.question_id
GROUP BY
    difficult_level
order by
    correct_rate