题解 | #各用户等级的不同得分表现占比#
各用户等级的不同得分表现占比
http://www.nowcoder.com/practice/ebff819fd38c46db8a42dfe43ca7b33a
SELECT DISTINCT level,score_grade,ratio FROM (SELECT level,score_grade,uid, ROUND(COUNT(score_grade)OVER(PARTITION BY level,score_grade)/COUNT(score_grade)OVER(PARTITION BY level),3) AS ratio FROM (SELECT er.uid,ui.level,er.exam_id,er.score, CASE WHEN score>=90 THEN '优' WHEN score>=75 THEN '良' WHEN score>=60 THEN '中' ELSE '差' END AS score_grade FROM exam_record er INNER JOIN user_info ui ON er.uid=ui.uid WHERE submit_time is not null)tt)tt ORDER BY level DESC,ratio DESC
