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

# select
#     user_profile.university as university,
#     round(count(question_practice_detail.question_id)/count(user_profile.device_id),4) as avg_answer_cnt
# from user_profile,question_practice_detail
# having user_profile.device_id=question_id.device_id
# group by university
# order by university desc

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