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

select
    difficult_level,
    sum(right_answ_num) / sum(answ_num) as correct_rate
from
    (
        select
            a.device_id,
            b.question_id,
            d.difficult_level,
            coalesce(b.answ_num, 0) as answ_num,
            coalesce(c.right_answ_num, 0) as right_answ_num
        from
            (
                select
                    device_id
                from
                    user_profile
                where
                    university = '浙江大学'
                group by
                    1
            ) a
            left join (
                select
                    device_id,
                    question_id,
                    count(question_id) as answ_num
                from
                    question_practice_detail
                group by
                    1,
                    2
            ) b on a.device_id = b.device_id
            left join (
                select
                    device_id,
                    question_id,
                    count(question_id) as right_answ_num
                from
                    question_practice_detail
                where
                    result = 'right'
                group by
                    1,
                    2
            ) c on a.device_id = c.device_id
            and b.question_id = c.question_id
            left join (
                select
                    question_id,
                    difficult_level
                from
                    question_detail
            ) d on b.question_id = d.question_id
    ) t1
where
    difficult_level is not null
group by
    1
order by
    2 asc