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

首先,得出题目的难度分布表 接着，与用户信息表连接后提取出计算的均值 #注意信息表的连接可能会出现null，所以明确好right join 还是left join

select u.university
  ,a.difficult_level
  ,count(u.answer_cnt)/ count(distinct a.device_id) as avg_acnt
from user_profile u
right join 
(
    select q.device_id,q.question_id,qd.difficult_level
    from question_practice_detail q
    left join question_detail qd
    ON q.question_id = qd.question_id
)a
on u.device_id = a.device_id
group by 1,2