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

select user_id as id
,name
,grade_num
from (
select
    user_id
    ,sum(
        case
            when type = 'add' then grade_num
            when type = 'reduce' then - grade_num
            else 0
        end
    ) as grade_num
    ,dense_rank() over (        
        order by
            sum(
                case
                    when type = 'add' then grade_num
                    when type = 'reduce' then - grade_num
                    else 0
                end
            )
    DESC ) as rk
from
    grade_info
group by
    user_id
) t join user u on t.user_id = u.id 
where rk = 1
order by id ASC;