题解 | #查询每天刷题通过数最多的前二名用户id和刷题数#

with A as(select 
                date,
                user_id,
                sum(pass_count) as pass_count
            from 
                questions_pass_record
            group by 
                date,
                user_id)
select 
    a.date,
    a.user_id,
    a.pass_count
from 
    (select 
        date,
        user_id,
        pass_count,
        row_number()over(partition by date order by pass_count desc) as cnt
    from 
        A)a
where 
    a.cnt<=2

临时表先保证每天同个用户的重复记录变为单行，再进行开窗排序，选择前2个即可。