题解 | 统计每个学校各难度的用户平均刷题数
统计每个学校各难度的用户平均刷题数
https://www.nowcoder.com/practice/5400df085a034f88b2e17941ab338ee8
# SQL语句的执行顺序
# 1.现根据学校和难度查询基本数据
# 2.按照分组后的不同学校不同难度，统计计算的平均值
# 平均值的计算是: 当前难度的问题数量 / 回答当前问题的学生数量
# -> count(qd.question_id) / count(distinct up.device_id);
# 同时注意要保留四位小数 round(x,4) -> as avg_answer_cnt
select up.university,
qd.difficult_level,
round(count(qd.question_id) / count(distinct up.device_id),4) as avg_answer_cnt
from user_profile as up
inner join question_practice_detail as qpd
on up.device_id = qpd.device_id
inner join question_detail as qd
on qd.question_id = qpd.question_id
group by up.university,qd.difficult_level
order by up.university asc;
