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

1.先分别取出用户得分不为0的分数，然后按照得分给其打上标签。 2.然后分别统计每个等级且有分数的用户数。 3.计算不同等级的得分占比。 4.按照等级和标签进行分组。 5.按照等级和占比降序输出。

SELECT level,score_grade,
round(count(uid)/total,3) as ratio
from 
(    SELECT u.uid as uid,
exam_id,score,level,
case when score >=90 then '优'
when score <90 and score >=75 then '良'
when score <75 and score >=60 then '中'
else '差' end as score_grade,
count(*) over(partition by level) as total
from user_info u join exam_record e on u.uid=e.uid
WHERE score is not NULL)a

group by level,score_grade
order by level desc,ratio desc;