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

with fd as (
select first_day,
round(count(distinct case when datediff(date,first_day)=1 then user_id end)*1.0/
count(distinct user_id),3)
 as p
from (
select user_id,date,
min(date) over(partition by user_id) as first_day
 from login
) t
group by first_day
)
select distinct date,ifnull(p,0.000) as p
from login l
left join fd on l.date=fd.first_day
order by date
;