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

select user_profile.university as university,
       count(question_practice_detail.question_id)/count(distinct question_practice_detail.device_id) as avg_answer_cnt
from question_practice_detail join user_profile on
     question_practice_detail.device_id = user_profile.device_id
group by university
order by university asc

原来distinct不仅可以写在select后面，还可以写在想去重的某个量的前面，就像这道题里，求每个大学的平均答题数，就是先以大学为聚合依据，然后用总答题条数（question_id）/id数（distinct device_id）得到各大学平均每人答题数。