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

select     #通过筛选及分组的方式计算每个学校的同学平均答题数
    b.university,
    b.difficult_level,
    sum(b.result_cnt)/count(distinct b.device_id) as avg_answer_cnt 
from
    (
    select    #关联每个同学所属的学校
        a.device_id,
        a.difficult_level,
        a.result_cnt,
        up.university
    from(
        select      #计算每位同学在不同难度下的答题数；
            qpd.device_id,
            qd.difficult_level,
            count(qpd.result) as result_cnt  
        from question_practice_detail as qpd
        left join question_detail as qd
        on qpd.question_id=qd.question_id
        group by qpd.device_id,qd.difficult_level
    ) as a
    left join user_profile as up
    on a.device_id=up.device_id
    ) as b
group by university,difficult_level