题解 | 牛客每个人最近的登录日期(六)

# 先链接表，看看各个用户的名字，登录日期以及各个日期的刷题情况：
# 把没刷题的抹掉,使用窗口函数
select
a.name as u_n,
a.date as date,
sum(a.number) over(partition by a.name order by a.date rows between unbounded preceding and current row) as ps_num
from
    (select
    l.user_id as user_id,
    u.name as name,
    l.date as date,
    p.number as number
    from
    login as l
    inner join
    user as u
    on l.user_id = u.id 
    left join
    passing_number as p
    on l.user_id = p.user_id and  l.date = p.date) a
where 
    a.number is not null
order by a.date