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

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

GROUP BY university,difficult_level

HAVING university = '山东大学'#只需在上面的答案 加上一句判断即可

ORDER BY university;