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

select
    c.university,
    sum(c.ansNum) / count(c.ansNum) as avg_answer_cnt
from
    (
        select
            a.university,
            count(*) ansNum
        from
            user_profile a
            join question_practice_detail b on a.device_id = b.device_id
        group by
            a.university,
            a.device_id
    ) c
group by
    c.university
order by
    c.university asc