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

# 思路：
# 1、提取非空的uid level score 用case when 生成不同得分等级变量
# 2、按照用户分组计算每个等级完成答题的数量finish_cnt
# 3、按照等级、finish_cnt,level分组，生成各uid各等级的数量rank_cnt
# 4、用rank_cnt/finish_cnt 计算ratio
select t2.level,t2.score_grade
,round(t2.level_score_rate/t2.finish_cnt,3) as ratio from (
select t1.level,t1.score_grade,t1.finish_cnt
,count(*) as level_score_rate from (
select e1.score,u1.level
,count(*) over(partition by u1.level) as finish_cnt
,(case when e1.score < 60 then '差'
  when (e1.score>=60 and e1.score < 75) then '中' 
        when (e1.score>=75 and e1.score < 90) then '良' 
  else '优' end ) as score_grade 
  from exam_record as e1 left join user_info as u1 
  on e1.uid=u1.uid where e1.submit_time is not null
  ) t1  group by t1.level,t1.finish_cnt,t1.score_grade) t2 
  order by t2.level desc,ratio desc