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

select
    qd.difficult_level,
    round(sum(correct_q) / sum(all_q), 4) as correct_rate -- 正确率=正确题目数量/所有题目数量
from
    (
        select
            qpd1.question_id,
            count(
                case
                    when qpd1.result = 'right' then qpd1.device_id
                    else null
                end
            ) correct_q, -- 正确题目数量
            count(qpd1.id) all_q -- 所有题目数量
        from
            (
                select
                    qpd.device_id,
                    qpd.question_id,
                    qpd.result,
                    up.university,
                    qpd.id
                from
                    question_practice_detail qpd
                    left join user_profile up on qpd.device_id = up.device_id
                where
                    up.university = '浙江大学' 
            ) qpd1 -- 限制计算范围为浙江大学
        group by
            question_id
    ) qpd2
    left join question_detail qd on qpd2.question_id = qd.question_id
group by 
    qd.difficult_level
order by correct_rate asc