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

select university,  round(question_count/student_count, 4) as avg_answer_cnt
from (
    select count(distinct a.device_id) as student_count, count(a.question_id) as question_count, b.university
    from question_practice_detail a
    left join user_profile b
    on a.device_id = b.device_id
    group by university
) as c
order by university;