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

SELECT b.qdiff,
       COUNT(if(b.result = 'right', 1, null)) / COUNT(1) AS correct_rate
FROM (SELECT device_id
      FROM user_profile
      WHERE university = '浙江大学') AS a
         INNER JOIN
     (SELECT qpd.device_id,
             qpd.question_id,
             qpd.result,
             qd.difficult_level AS qdiff
      FROM question_practice_detail AS qpd
               INNER JOIN question_detail AS qd
                          ON qd.question_id = qpd.question_id) AS b
     ON a.device_id = b.device_id
GROUP BY b.qdiff
ORDER BY correct_rate;