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

with tt as (
    select user_id, date, min(date) over (partition by user_id) as first_day
    from login
),
tb as (
    select 
        user_id, 
        if(date = first_day, 1, 0) as is_first, 
        date,
        first_day
    from tt
)
select date,
ifnull(count(distinct case when is_first = 1 then user_id else null end),0) as new  
from tb
group by date