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

with s1 as(select level,score_grade,count(score_grade) grade_cnt from(
select level,
case when score < 60 then '差' when score >=60 and score <75 then '中' when score >=75 and score <90 then '良' else '优' end score_grade 
from exam_record er left join user_info ui
on er.uid = ui.uid
where submit_time is not null) a
group by level,score_grade)
select level,score_grade,round(grade_cnt/sum(grade_cnt)over(partition by level),3) ratio
from s1
order by level desc,ratio desc;