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

正确

SELECT difficult_level,
    sum(if(result="right", 1, 0)) / COUNT(qpd.question_id) as correct_rate
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 university = "浙江大学"
GROUP BY difficult_level
ORDER BY correct_rate;

错误原因：误以为题目总数为question_cnt

SELECT difficult_level,
    sum(if(result="right", 1, 0)) / question_cnt as correct_rate
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 university = "浙江大学"
GROUP BY difficult_level
ORDER BY correct_rate;