题解 | #统计每个用户的平均刷题数#

采用HAVING：

SELECT
    up.university,
    qd.difficult_level,
    COUNT(qpd.question_id) / COUNT(DISTINCT(qpd.device_id)) AS avg_answer_cnt
FROM question_practice_detail AS qpd
LEFT JOIN user_profile AS up
ON qpd.device_id=up.device_id
LEFT JOIN question_detail AS qd
ON qpd.question_id=qd.question_id
GROUP BY up.university, qd.difficult_level
HAVING up.university='山东大学'

采用WHERE：

SELECT
    up.university,
    qd.difficult_level,
    COUNT(qpd.question_id) / COUNT(DISTINCT(qpd.device_id)) AS avg_answer_cnt
FROM question_practice_detail AS qpd
LEFT JOIN user_profile AS up
ON qpd.device_id=up.device_id
LEFT JOIN question_detail AS qd
ON qpd.question_id=qd.question_id
WHERE up.university='山东大学'
GROUP BY qd.difficult_level