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

with tiaojian as (
select 
date
from login
group by date
),tiaojian1 as (
select
t.m,
round(
count(distinct  case when datediff(t.p,t.m)=1 then t.user_id end)/count(distinct t.user_id),3) as cnt
from(
select 
user_id,
min(date)over(partition by user_id) as m,
lead(date,1)over(partition by user_id order by date) as p
from login
) as t
group by t.m
)


select 
t.date,
ifnull(t1.cnt,0)
from tiaojian t left join tiaojian1 t1 on t.date=t1.m
order by t.date asc