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

# 注意，这一题是新用户的次日留存率 并且是计算每一天
# 首先是考虑 新用户 （新用户可以通过第一次登陆的时间来判断）  然后是考虑 留存率
with t1 as (
    select
        distinct user_id,
        date,
        first_value(date) over (partition by user_id order by date asc rows between unbounded preceding and unbounded following) as first_date,
        lead(date, 1, 0) over (partition by user_id order by date asc) as next_date
    from   
        login
)
select 
    date,
    ifnull(round(sum(if(date=first_date and date_sub(next_date, interval 1 day)=date, 1, 0)) / sum(if(date=first_date, 1, 0)), 3), 0) as p 
    # 计算出来新用户的数量，然后计算新用户中次日登录的数量，进行相除，如果分母为0，则填充为0
from     
    t1
group by 
    date
order by 
    date asc;