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

# 计算次日留存率 
# 去重
with t1 as (
    select
        distinct user_id,
        date,
        row_number() over (partition by user_id order by date asc) as ranking
    from
        login
),
# 筛选出新登录用户的数据 （时间最早的前两个）
t2 as (
    select
        user_id,
        date
    from
        t1
    where
        ranking <= 2
),

# 加入次日的登录记录
t3 as (
    select
        user_id,
        date,
        lead(date ,1 ,0) over (partition by user_id order by date asc) as next_date
    from
        t2
)
# 计算留存率
select 
    round(sum(if(date_sub(next_date, interval 1 day) = date, 1, 0)) / count(distinct user_id), 3) as p
from    
    t3;