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

-- 统计不同用户等级的人完成的试卷数
-- 统计不同用户等级的人各得分等级试卷数

SELECT level, 
    score_grade,
    ROUND(
        COUNT(uid) / total_cnt, 3
    ) AS ratio
FROM (
    SELECT level, uid,
        ( CASE 
             WHEN score >= 90 THEN '优'
             WHEN score >= 75 THEN '良'
             WHEN score >= 60 THEN '中'
             ELSE '差'
          END
        ) AS score_grade,
        COUNT(*) OVER(PARTITION BY level) AS total_cnt
    FROM exam_record 
        JOIN user_info
        USING(uid)
    WHERE score IS NOT NULL
) grade_t
GROUP BY level, score_grade
ORDER BY level DESC, ratio DESC