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

select e.university,e.difficult_level,count(e.question_id) / count(distinct e.device_id)
from
(select c.*,d.difficult_level 
from
(select a.device_id,a.question_id,
       b.university
from
question_practice_detail a
left join 
user_profile b
on a.device_id=b.device_id)c
join
question_detail d
on c.question_id=d.question_id)e
group by 
e.university,e.difficult_level