题解 | 获得积分最多的人(三)

with
t1 as(
    select
        user_id,
        (
            case
                when type='add' then grade_num
                else -grade_num
            end
        ) as grade_num
    from
        grade_info
),
t2 as(
    select
        user_id,
        sum(grade_num) as grade_num
    from
        t1
    group by
        user_id
),
t3 as(
    select
        id,
        name,
        grade_num
    from
        t2 left join user on t2.user_id=user.id
),
t4 as(
    select
        id,
        name,
        grade_num,
        dense_rank()over(order by grade_num desc) as g_rank
    from
        t3
)

select
    id,
    name,
    grade_num
from
    t4
where
    g_rank=1
order by
    id