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

with
    base as (
        select
            qpd.device_id as device_id,
            qpd.question_id as question_id,
            result,
            difficult_level
        from
            question_practice_detail qpd
            left join user_profile u on u.device_id = qpd.device_id
            left join question_detail qd on qpd.question_id = qd.question_id
        where
            university = '浙江大学'
    ),
    tmp as (
        select
            difficult_level,
            count(question_id) as total_questions,
            sum(
                case
                    when result = 'right' then 1
                    else 0
                end
            ) as right_cn
        from
            base
        group by
            difficult_level
    )
select difficult_level,round(right_cn/total_questions,4) correct_rate from tmp order by round(right_cn/total_questions,4) asc