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

with new_login as (
    select 
        ifnull(cnt, 0) as new_cnt,
        login.date
    from
        login 
        left join (
            select
                count(*) as cnt,
                date
            from login
            where (user_id, date) in (select user_id, min(date) from login group by user_id)
            group by date
        )as first_login on login.date = first_login.date
    group by date
),
keep_user as (
    select 
        count(distinct user_id) as keep_cnt,
        date
    from login
    where (user_id, date) in 
        (select user_id, date_add(min(date), interval 1 day) from login group by user_id)
    group by date
)
select
    new_login.date,
    round(ifnull(keep_cnt/new_cnt, 0), 3) as p
from    
    new_login
    left join keep_user on new_login.date = date_sub(keep_user.date, interval 1 day)