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

/* select university,round(count(q.question_id)/count(distinct(q.device_id)),4) as avg_answer_cnt 
from user_profile,question_practice_detail q 
where user_profile.device_id=q.device_id 
group by university 
order by university asc
*/

select university, round(avg(num),4) from
(select device_id,count(1) as num from question_practice_detail 
group by device_id ) q left join user_profile on user_profile.device_id =q.device_id
group by university order by university