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

此题逻辑不复杂，主要分为两步。

① 将所有表连接起来，并提取关键字段信息

select
    up.device_id,
    up.university,
    qpd.question_id,
    qpd.result,
    qd.difficult_level
from user_profile as up
join question_practice_detail as qpd
on up.device_id = qpd.device_id
join question_detail as qd 
on qpd.question_id = qd.question_id
where up.university = "浙江大学"

② 通过group by聚合difficult_level，计算答题正确率，即正确答题数除以总题数，即可获得最终结果

select 
    difficult_level,
    sum(if(result = "right", 1, 0)) / count(result) as correct_rate 
from (select
          up.device_id,
          up.university,
          qpd.question_id,
          qpd.result,
          qd.difficult_level
      from user_profile as up
      join question_practice_detail as qpd
      on up.device_id = qpd.device_id
      join question_detail as qd 
      on qpd.question_id = qd.question_id
      where up.university = "浙江大学") as t
group by difficult_level
order by correct_rate