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

 #先将每道题匹配上他的难易程度
# select p.*,q.difficult_level from  question_practice_detail as p left join  question_detail as q using(question_id)
#过滤出为浙江大学的学生，在对其做题难易程度进行分组。
#计算正确和错误题目的正确率，可以使用case语句，正确为1，错误为0.在使用sum()函数进行统计即可。

select
    difficult_level,
    sum(
        case
            when a.result = 'right' then 1
            else 0
        end
    ) / count(a.result) as correct_rate
from
    user_profile u
    join (
        select
            p.*,
            q.difficult_level
        from
            question_practice_detail as p
            left join question_detail as q using (question_id)
    ) as a using (device_id)
where
    u.university = '浙江大学'
group by
    a.difficult_level
order by
    correct_rate