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

with t as (
    select user_id,date,lead(date,1)over(partition by user_id) as dtime
    from login
#     where (user_id,date) in (select user_id,min(date) from login group by user_id)
)

select distinct t2.date,
       case when rate is not null then rate
            else 0.000
            end as p
from
(select date,round(sum(datediff(dtime,date)=1)/count(1),3) as rate
from t
where (user_id,date) in (select user_id,min(date) from login group by user_id)
group by date) t1
right join login t2 on t1.date = t2.date