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

with
    t1 as (
        select
            b.user_id,b.type type ,
            grade_num - coalesce(
                lead (grade_num, 1) over (
                    partition by
                        user_id
                    order by
                        type
                ),
                0
            ) num
        from
            (
                select
                    user_id,
                    type,
                    sum(grade_num) grade_num
                from
                    grade_info a
                group by
                    user_id,
                    type
            ) b
    )
        
        select c.id,d.name,c.grade_num
        from
        (
        select
            t1.user_id id,
            t1.num grade_num,
            rank() over( order by t1.num desc) rn
        from t1 
        where t1.type='add'
        )c
        left join user d
        on c.id=d.id
        where c.rn=1
        order by c.id