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

select a.university,c.difficult_level,round((count(b.question_id))/(count(distinct b.device_id)),4) as avg_answer_cnt
from question_practice_detail as b
left join user_profile as a on b.device_id = a.device_id
left join question_detail as c on b.question_id = c.question_id
group by a.university,c.difficult_level

首先明确有两个分组条件 学校和问题难度 使用group by

接着，观察三张表发现核心问题在第二张表，只有第二张表的id才是主键

最后需要计算人均回答问题数，注意distinct去重，count计数，round保留4位有效数字