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

select university,
    difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id)
    from question_practice_detail as qpd 
    left join user_profile as up
    on qpd.device_id = up.device_id
    left join question_detail as qd
    on qpd.question_id = qd.question_id
    group by university,difficult_level

1. 表连接：

   question_practice_detail 的 device_id 与 user_profile 的 id 连接

   question_practice_detail 的 question_id 与 question_detail 的 id 连接

2. 每个学校：group by university

3. 不同难度：group by difficult_level

4. 平均刷题数 = 总题目数 / 总人数