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

WITH temp AS(    
    SELECT a.device_id,b.result,c.difficult_level
    FROM(    
        SELECT device_id,university
        FROM user_profile
        WHERE university = '浙江大学'
    ) AS a
    JOIN question_practice_detail AS b ON a.device_id = b.device_id
    JOIN question_detail AS c ON b.question_id = c.question_id
) 

SELECT difficult_level,round(avg(result = 'right'),4) correct_rate
FROM temp 
GROUP BY difficult_level
ORDER BY correct_rate ASC

这个计算的时候不用考虑未答题的情况，直接使用JOIN 或 INNER JOIN就行了，LEFT JOIN会出错