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

#使用表连接的方式，多次连接，得到结果；

select

    b.university,

    avg(b.question_cnt) as avg_question_cnt

from (

    select

        university,

        a.device_id,

        question_cnt

    from user_profile as m

    left join(

        select

            device_id,

            count(question_id) as question_cnt

        from question_practice_detail

        group by device_id) as a

    on m.device_id=a.device_id

) as b

group by b.university

order by university