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

解题思路：

1.完成了的试卷对应的用户等级和分数等级

2.统计不同用户等级的得分等级占比(结果保留3位小数)

3.按用户等级降序、占比降序排序。

步骤：

1.完成了的试卷对应的用户等级和分数等级

select 
a.uid,
exam_id,
score,
level,
case when score>=90 then '优'
	when score<90 and score>=75 then '良'
	when score<75 and score >=60 then '中'
	when score<69 then '差'
	end as score_grade,
count(a.uid) over(partition by level) cnt
from exam_record a
left join user_info b
on a.uid=b.uid
where score is not null

2.统计不同用户等级的得分等级占比(结果保留3位小数)

select
level,
score_grade,
round(count(*)/cnt,3) ratio
from (
select 
a.uid,
exam_id,
score,
level,
case when score>=90 then '优'
	when score<90 and score>=75 then '良'
	when score<75 and score >=60 then '中'
	when score<69 then '差'
	end as score_grade,
count(a.uid) over(partition by level) cnt
from exam_record a
left join user_info b
on a.uid=b.uid
where score is not null)t

3.按用户等级降序、占比降序排序。

select
level,
score_grade,
round(count(*)/cnt,3) ratio
from (
select 
a.uid,
exam_id,
score,
level,
case when score>=90 then '优'
	when score<90 and score>=75 then '良'
	when score<75 and score >=60 then '中'
	when score<69 then '差'
	end as score_grade,
count(a.uid) over(partition by level) cnt
from exam_record a
left join user_info b
on a.uid=b.uid
where score is not null)t
group by level,score_grade,cnt
order by level desc,ratio desc