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

select u.university,qd.difficult_level,
round(count(q.question_id)/count(distinct u.device_id),4) as avg_answer_cnt#这里不要忘记加上distinct，不然每个平均值都是1
from user_profile as u
left join question_practice_detail as q#如果qpd表记录的是所有学生答的所有题，那么用inner join,但是如果qpd不全，必须用left join，因为inner join会漏掉一部答了题但是答题明细没记录在qpd表的学生，使得count(distinct u.device_id)偏小
on u.device_id=q.device_id
inner join question_detail as qd
on q.question_id=qd.question_id
group by university,difficult_level