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

WITH tmp_user_level AS (
	SELECT
		ui.level,
	CASE	
			WHEN er.score >= 90 THEN
			'优' 
			WHEN er.score >= 75 THEN
			'良' 
			WHEN er.score >= 60 THEN
			'中' ELSE '差' 
		END AS score_grade 
	FROM
		exam_record er
		LEFT JOIN user_info ui ON er.uid = ui.uid 
	WHERE
		er.score IS NOT NULL 
	) 
	
 
	
SELECT
	tul.level level,
	tul.score_grade score_grade,
	ROUND(count(*) / tt.ccc ,3) ratio
FROM
	tmp_user_level tul
LEFT JOIN (SELECT count(1) ccc ,level FROM tmp_user_level GROUP BY level)  tt on tt.level = tul.level
GROUP BY tul.level, tul.score_grade,tt.ccc
ORDER BY tul.level DESC,ratio DESC