题解 | 分组后再聚合解法
统计每个学校的答过题的用户的平均答题数
https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5
SELECT
t.university,
AVG(t.cnt) AS avg_answer_cnt
FROM (
SELECT
u.university,
d.device_id,
COUNT(*) AS cnt
FROM question_practice_detail d
LEFT JOIN user_profile u
ON u.device_id = d.device_id
GROUP BY u.university, d.device_id
) t
GROUP BY t.university
order by t.university,avg_answer_cnt
查看6道真题和解析