题解 | #各用户等级的不同得分表现占比#

# 先找出完成过试卷的用户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;