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

with a as(
    select distinct user_id,min(date) as date
    from login
    group by user_id),
b as(
    select distinct date
    from login
)
select b.date as date,case when count(a.user_id) is not null then count(a.user_id) else 0 end as new
from a right join b using(date)
group by b.date
order by b.date

1、计算每个用户的首次登录日期，记得分组

2、注意：有些日期没有新登录用户，所以链接一定要注意