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

#  2个表都需要


# select * from user_profile u,question_practice_detail q where u.device_id=q.device_id;
# select university,count(u.device_id) d from user_profile u,question_practice_detail q where u.device_id=q.device_id group by university
select a.university,b.q1/a.d from 
(select university,count(distinct u.device_id) d from user_profile u,question_practice_detail q where u.device_id=q.device_id group by university) a 
,
(select university,count(question_id) q1 from user_profile u,question_practice_detail q where u.device_id=q.device_id group by university) b

where a.university=b.university

先查询总人数（需要去重），在查询总题数