题解 | #统计每个学校的答过题的用户的平均答题数#

select
    university,
    avg(num) as avg_answer_cnt
from
    (
        select
            university,
            count(*) as num
        from
            (
                select
                    a.device_id,
                    university
                from
                    user_profile a
                    inner join (
                        select
                            device_id
                        from
                            question_practice_detail
                    ) b on a.device_id = b.device_id
            ) c
        group by
            university,
            device_id
    ) d
group by
    university
    order by university;