题解 | 统计每个学校的答过题的用户的平均答题数

这道题在做什么?

• 统计每个大学的平均做题数量

怎么拆分这个问题?

• 先根据u, di分组, count(*) as answer_count

SELECT u.university, u.device_id, COUNT(*) AS answer_cnt
FROM user_profile AS u
JOIN question_practice_detail AS q ON u.device_id = q.device_id
GROUP BY u.university, u.device_id

• 再根据学校分组, sum(answer_count) / count(*)

SELECT t.university, SUM(t.answer_cnt
)/COUNT(*) as avg_answer_cnt
FROM (
SELECT u.university, u.device_id, COUNT(*) AS answer_cnt
FROM user_profile AS u
JOIN question_practice_detail AS q ON u.device_id = q.device_id
GROUP BY u.university, u.device_id
) t
GROUP BY t.university
ORDER BY t.university;