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

with t as 
(select t.uid,exam_id,score,level,
       case when score>=90 then '优' 
            when score>=75 and score<90 then '良'
            when score>=60 and score<75 then '中'
            else '差' end as score_grade,
       count(exam_id)over(partition by level) as total
from user_info t 
join exam_record t1 using(uid)
where score is not NULL)
select level,score_grade,round(count(level)/total,3) as ratio
from t
group by level,score_grade
order by level desc,ratio desc