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

select 
    t2.university,
    round(sum(answer_cnt)/count(*),4) avg_answer_cnt
from
    user_profile t2
left join
    (select 
        question_practice_detail.device_id,
        count(*) answer_cnt
    from
        question_practice_detail
    group by
        device_id) t1
on
    t2.device_id = t1.device_id
where 
    t1.device_id is not null
group by
    t2.university
order by
    t2.university