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

with 
newdetail as(
    select
        user_id,
        date,
        case 
            when row_number() over(partition by user_id order by date asc)=1
            then 1 else 0 end as is_new
    from 
        login),
new_user as(
    select date,user_id
    from newdetail
    where is_new=1)

select
    login.date,
    case
        when round(t2.cnt*1.0/t1.cnt,3)<>0  
        then round(t2.cnt*1.0/t1.cnt,3)
        else round(0,3) 
        end as p
from
    login
left join
    (select
        date,count(distinct user_id) as cnt
    from 
        new_user group by date
    ) t1
on login.date=t1.date
left join
    (select
        u.date,count(distinct l.user_id) as cnt
    from new_user u left join login l
    on u.user_id=l.user_id and l.date=date_add(u.date,interval 1 day)
    group by u.date
    )t2
on 
    t1.date=t2.date
group by
    login.date
order by
    login.date asc