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

select
    university,
    difficult_level,
    round(avg(one_answer_cnt), 4) as avg_answer_cnt
from
    (
        select
            university,
            c.difficult_level,
            count(a.device_id) as one_answer_cnt
        from
            user_profile a
            inner join question_practice_detail b on a.device_id = b.device_id
            inner join question_detail c on b.question_id = c.question_id
        group by
            university,
            a.device_id,
            c.difficult_level
    ) d
group by
    university,
    difficult_level order by university asc

解题思路：

1、联表获得学校，设备id，题目id，题目难度信息，然后通过分组获得每个学校每个设备在每个题目难度下的答题总数

2、将步骤1获得数据作为临时表，通过分组统计平均数并格式化数据按学校升序展示（展示隐藏条件，看示例可得）得到题解