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

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