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

with min_login as (
    select
        user_id,
        min(date) as min_date
    from 
        login
    group by 
        user_id
)

select
    l.date,
    count(distinct 
        case 
            when l.date = m_l.min_date then l.user_id
            else null
        end) as new
from 
    login as l
inner join 
    min_login as m_l
on 
    l.user_id = m_l.user_id
group by 
    l.date