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

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