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

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


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