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

select up.university,format(COUNT(*) / (select count(*)
        from user_profile up1
        where up1.university = up.university and up1.device_id in (select device_id from question_practice_detail)
        group by up1.university),4)
       as avg_answer_cnt
from user_profile up
         inner join question_practice_detail qpd on up.device_id = qpd.device_id
group by up.university
order by up.university;