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

# select a.device_id,b.university,a.question_id,a.result
# from 
# question_practice_detail a
# left join
# user_profile b
# on a.device_id=b.device_id


select c.university,(count(c.university)/count(distinct c.device_id))avg_answer_cnt
from
(select a.device_id,b.university,a.question_id,a.result
from 
question_practice_detail a
left join
user_profile b
on a.device_id=b.device_id)c
group by
c.university