题解 | #牛客每个人最近的登录日期(五)-窗口函数解法#

select
date
,if(p is null,0.000,p) p
from 
(-- 全日期
select
date 
from 
login
group by date
) a 
left join
(-- 有次日登录数据的日期的次日登录率
select 
first_login_date
,cast(count(distinct if(datediff(date,first_login_date) = 1,user_id,null))/count(distinct user_id) as decimal(8,3)) p 
from
(select
    user_id 
    ,date
    -- 每个user_id的首次登陆日期
    ,min(date) over(partition by user_id) first_login_date
from 
    login 
) a 
group by first_login_date ) b 
on a.date = b.first_login_date
order by date