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

select
    level,
    score_grade,
    # 统计得分占比
    round(count(uid) / total, 3) as ratio
from
    (
        select
            u_i.uid as uid,
            exam_id,
            score,
            level,
            # 试卷得分分层
            case
                when score >= 90 then "优"
                when score >= 75 then '良'
                when score >= 60 then '中'
                else '差'
            end as score_grade,
        # 题目要求是统计不同用户等级的人，所以要按照level分组统计次数
            count(*) over (
                partition by
                    level
            ) as total
        from
            user_info u_i
            join exam_record e_r on u_i.uid = e_r.uid
        # 未完成试卷的用户无需输出
        where
            score is not null
    ) as user_grade_table
group by
    level,
    score_grade
order by
    level desc,
    ratio desc