题解 | #各用户等级的不同得分表现占比#
各用户等级的不同得分表现占比
https://www.nowcoder.com/practice/ebff819fd38c46db8a42dfe43ca7b33a
# 先找出完成过试卷的用户uid
with t1 as (
select a.uid, b.level, a.score
from exam_record as a
left join user_info as b
on a.uid = b.uid
),
# 将各个分数所对应的等级表示出来
t2 as (
select uid, level,
(case when score < 60 then '差'
when score >= 90 then '优'
when score >= 75 and score < 90 then '良'
else '中' end) as score_grade
from t1
where score IS NOT NULL
),
# 根据每个用户的等级的不同分数性质进行分组，统计个数
t3 as (
select level, score_grade, count(uid) as cnt
from t2
group by level, score_grade
),
# 根据用户等级进行分组
t4 as (
select level, count(uid) as total_cnt
from t2
group by level
)
# 最后左连接进行比率的计算
select t3.level, t3.score_grade, round(t3.cnt/t4.total_cnt, 3) as ratio
from t3
left join t4
on t3.level = t4.level
order by level desc, ratio desc;
投递网易有道等公司10个岗位