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

with tb as(
select score,level, 
count(level) over(partition by level) as answer_cnt,  #添加记录每个等级用户答卷数量
case when score>= 90 then '优'
     when score<90 and score >=75 then '良'
     when score<75 and score >=60 then '中'
     else '差' end as score_grade
from exam_record 
left join user_info using(uid)
where score is not null)

select level, score_grade, 
round(count(level)  / answer_cnt ,3)as ratio
from tb
group by level,score_grade
order by level desc,ratio desc