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

# 每个成绩都有等级：case when[90,75,60]分为优良中差
# 根据每个人分组，sum(case when)


select level,score_grade,
round(count(score)/max(total_cnt),3) as ratio
from
(
    select ui.uid,level,score,
    (case when score between 90 and 100 then '优' when score between 75 and 89 then '良' when score between 60 and 74 then '中' else '差' end) as score_grade,
    count(score)over(partition by level) as total_cnt
    from exam_record er join user_info ui using(uid)
    where score is not null
)a
group by level,score_grade
order by level desc,ratio desc