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

注意：截止日期是最后一次登录的日期，所以每个用户不完全一致

select u.name as u_n
         ,l.date as date
         , sum(pn.number) over (partition by l.user_id order by pn.date) as ps_num
from login l
left join passing_number pn
on l.user_id = pn.user_id
and l.date = pn.date
left join user u 
on l.user_id = u.id
where pn.id is not null
order by pn.date, u.name
;

难点：怎么算每一个截止当前某一日期

解决：窗口函数里面，窗口里的日期要升序而不是降序