题解 | 统计每个学校各难度的用户平均刷题数

SELECT university, difficult_level,ROUND(AVG(cnt),4) as avg_answer_cnt
FROM
(

SELECT
  university,
  difficult_level,
  user_profile.device_id,
  COUNT(question_practice_detail.question_id) as cnt
FROM user_profile 
INNER JOIN question_practice_detail  
ON user_profile.device_id = question_practice_detail.device_id 
INNER JOIN question_detail
ON question_practice_detail.question_id = question_detail.question_id
GROUP BY university,  difficult_level,device_id
) join_table#1 仍然使用子表进行操作，在进行子表操作后，山东大学的数据项为以下
# university difficult_level device_id cnt
# 山东大学    easy               5432   6
# 山东大学    medium             5432   3
# 山东大学    easy               2131   3

GROUP BY university,difficult_level #2对于子表只需要进行一次聚合就可以
ORDER BY university;