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

select
    university,
    difficult_level,
    count(*) / count(distinct device_id) as avg_answer_cnt
from
    (
        select
            c.question_id,
            difficult_level,
            e.university,
            e.device_id
        from
            question_detail c
            inner join (
                select
                    a.device_id,
                    university,
                    b.question_id
                from
                    user_profile a
                    inner join (
                        select
                            device_id,
                            question_id
                        from
                            question_practice_detail
                    ) b on a.device_id = b.device_id
            ) e on c.question_id = e.question_id
    ) d
group by
    difficult_level,
    university
order by
    university;