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

with
    score as (
        select
            er.uid,
            exam_id,
            score,
            level,
            case
                when score >= 90 then '优'
                when score >= 75 then '良'
                when score >= 60 then '中'
                else '差'
            end score_grade,
            count(
                case
                    when score >= 90 then '优'
                    when score >= 75 then '良'
                    when score >= 60 then '中'
                    else '差'
                end
            ) over (
                partition by
                    level
            ) 计数
        from
            test.user_info ui
            join test.exam_record er on ui.uid = er.uid
        where
            score is not null
    )
select
    level,
    score_grade,
    round(count(uid) / 计数, 3) ratio
from
    score
group by
    level,
    score_grade,
    计数
order by
    level desc,
    ratio desc