解体解答SQL150
各用户等级的不同得分表现占比
https://www.nowcoder.com/practice/ebff819fd38c46db8a42dfe43ca7b33a
第一步，判断每个分数所属区间
select u1.uid,level,exam_id,score,case when score >= 90 then '优'
when score < 90 && score >= 75 then '良'
when score < 75 && score >= 60 then '中'
else '差' end score_grade
from user_info u1 join exam_record e1
on u1.uid = e1.uid
where score is not null
第二步，求得每个level,每个score_grade相应的数量
select level,score_grade,count(*) ct from (
select u1.uid,level,exam_id,score,case when score >= 90 then '优'
when score < 90 && score >= 75 then '良'
when score < 75 && score >= 60 then '中'
else '差' end score_grade
from user_info u1 join exam_record e1
on u1.uid = e1.uid
where score is not null
)t1
group by level,score_grade
第三步，求得每个level一共多少数量
select level,score_grade,ct,sum(ct) over(partition by level) sum_ct from(
select level,score_grade,count(*) ct from (
select u1.uid,level,exam_id,score,case when score >= 90 then '优'
when score < 90 && score >= 75 then '良'
when score < 75 && score >= 60 then '中'
else '差' end score_grade
from user_info u1 join exam_record e1
on u1.uid = e1.uid
where score is not null
)t1
group by level,score_grade
)t2
第四步，用第二步求得数量除第三步的数量
select level,score_grade,round(ct/sum_ct,3) ratio from(
select level,score_grade,ct,sum(ct) over(partition by level) sum_ct from(
select level,score_grade,count(*) ct from (
select u1.uid,level,exam_id,score,case when score >= 90 then '优'
when score < 90 && score >= 75 then '良'
when score < 75 && score >= 60 then '中'
else '差' end score_grade
from user_info u1 join exam_record e1
on u1.uid = e1.uid
where score is not null
)t1
group by level,score_grade
)t2
)t3
order by level desc ,ratio desc
