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

方法一
SELECT
    university,
    difficult_level,
    COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt
FROM user_profile u,
     question_detail q,
     question_practice_detail q_p
WHERE
    u.device_id = q_p.device_id
    and q_p.question_id = q.question_id
    AND u.university = "山东大学"
GROUP BY difficult_level

方法二：
SELECT
    university,
    difficult_level,
    COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt
FROM question_detail q
JOIN question_practice_detail q_p
ON q_p.question_id = q.question_id
JOIN user_profile u
ON u.device_id = q_p.device_id
AND u.university = "山东大学"
GROUP BY difficult_level