题解 | 统计每个学校各难度的用户平均刷题数（真小白写给自己看版）

select  u.university,qd.difficult_level, 
round(count(q.device_id) /count(distinct q.device_id) ,4)as avg_answer_cnt
from user_profile as u
join question_practice_detail as q on u.device_id = q.device_id
join question_detail as qd on q.question_id = qd.question_id
GROUP BY university,qd.difficult_level
ORDER BY u.university, qd.difficult_level;  

脑子里命名三个表格的名字：

user_profile as u

question_detail as qd

question_practice_detail as q

其中select中的三个会在最后输出

SELECT 
  u.university,  -- 输出列1：学校名称
  qd.difficult_level,  -- 输出列2：题目难度级别
  ROUND(COUNT(q.device_id) / COUNT(DISTINCT q.device_id), 4) AS avg_answer_cnt  -- 输出列3：平均答题量（计算结果）

这三个部分就是最终结果要显示的三列，对应题目要求的university、difficult_level、avg_answer_cnt。

链接三个表格：

from user_profile as u

join question_practice_detail as q on u.device_id = q.device_id

join question_detail as qd on q.question_id = qd.question_id

最后分组排序一下真难