题解 | 统计每个用户的平均刷题数(两种解法)

# 两种解法

# 解法一（where条件，注意要在group by之前写where，group by之后写having）
select
    a.university,
    c.difficult_level,
    round(count(b.question_id) / count(distinct a.device_id), 4) as avg_answer_cnt
from
    user_profile as a
    inner join question_practice_detail as b on a.device_id = b.device_id
    inner join question_detail as c on b.question_id = c.question_id
where
    a.university = '山东大学'
group by
    a.university,
    c.difficult_level;

# 解法二（直接在inner join的on条件里写过滤条件也行）
select
    a.university,
    c.difficult_level,
    round(count(b.question_id) / count(distinct a.device_id), 4) as avg_answer_cnt
from
    user_profile as a
    inner join question_practice_detail as b on a.device_id = b.device_id and a.university = '山东大学'
    inner join question_detail as c on b.question_id = c.question_id
group by
    a.university,
    c.difficult_level;