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

select e.university,e.difficult_level,(count(e.difficult_level)/count(distinct e.question_id))
from
(
select c.question_id,c.result,c.university,d.difficult_level
from
(select a.device_id,a.question_id,a.result,b.university
from
question_practice_detail a
left join
user_profile b
on a.device_id=b.device_id
where b.university='山东大学')c
join
question_detail d
on c.question_id=d.question_id)e
group by
e.difficult_level
order by (count(e.difficult_level)/count(distinct e.question_id)) desc