题解 | #统计每个用户的平均刷题数#

select #筛选出学校=山东大学用户在不同难度下的答题数
    up.university,
    a.difficult_level,
    sum(a.result_cnt)/count(distinct a.device_id) as avg_answer_cnt
from 
(
    select   #先使用问题id将问题难度与用户id关联，并计算出不同难度的答题数
        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
    having count(qpd.result)>0
) as a
left join user_profile as up
on up.device_id=a.device_id
where university='山东大学'
group by     
    up.university,
    a.difficult_level