题解 | #统计每个用户的平均刷题数#

select
    university,
    difficult_level,
    count(question_detail.question_id) / count(distinct user_profile.device_id) as 'avg_answer_cnt'
from
    user_profile
inner join
    question_practice_detail
on
    user_profile.device_id = question_practice_detail.device_id
inner join
    question_detail
on 
    question_detail.question_id = question_practice_detail.question_id
group by
    university, difficult_level
having
    university = '山东大学'

1、3表连接， 内连就行，没有null数据

2、按照学校和难度分组

3、平均刷题数： question_detail表中 题数 / user_profile表中 人数