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

select s.date,
sum(case when s.is_new_user=1 then 1 else 0 end) as new
from
(select date,
case when date=min(date) over (partition by user_id) then 1 else 0 end as is_new_user
from login) as s
group by s.date

首先按用户id把第一次登陆日期，即min(date）找出来，即select date,

case when date=min(date) over (partition by user_id) then 1 else 0 end as is_new_user

from login

输出结果如图

实际输出

然后基于上述查询结果，求和满足最早登陆日期的天数，以日期进行分组group by