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

服了我做的好复杂
with u as
(select user_id,date,dense_rank()over(partition by user_id order by date)as rk
from login l),

a as
(select user_id,date,rk
from u
group by user_id,date,rk),

b as 
(select x.user_id,x.date as start,y.date as end
from a x
left join a y 
on x.user_id=y.user_id and x.rk=y.rk-1
where x.rk=1)

select round(sum(if(datediff(end,start)=1,1,0))/count(*),3) as p
from b