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

select login.date,
case when b.user_id is NULL then 0
else b.new_id
end as new #将没有新用户（null）的日期对应的new值赋0
from login left join
(select *,count(user_id) as new_id
from
(select * ,rank() over(PARTITION by user_id order by date) as number
from login) as a 
where a.number==1
group by date) as b #找到每个user第一天登陆日期
on login.date==b.date #左外连接，保留login中没有新用户的日期
group by login.date