题解 | #统计每个学校各难度的用户平均刷题数#

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