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

select
    date,
    sum(is_new_user) as new
from
    (
        select
            id,
            l.user_id,
            client_id,
            date,
            first_day,
            (
                case
                    when datediff (first_day, date) = 0 then 1
                    else 0
                end
            ) as is_new_user
        from
            login l
            left join (
                select
                    user_id,
                    min(date) as first_day
                from
                    login
                group by
                    user_id
            ) n on l.user_id = n.user_id
    ) a
group by
    date