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

with rank_date as (
select user_id,date
,row_number() over(partition by user_id	order by date) as rn
from login
),
first_date as (
    select user_id,date as first_day 
    from rank_date
    where rn=1
),
second_date as (
    select fd.user_id 
    from first_date fd
    join login l on l.user_id=fd.user_id 
    where date_add(first_day,interval 1 day)=date
)
select round(count(sd.user_id)*1.0/count(distinct fd.user_id),3) as  p
from first_date fd
left join second_date sd on sd.user_id=fd.user_id;