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

# 先找到每一个用户的首次登陆时间
with t1 as (
    select user_id, min(date) as first_date
    from login 
    group by user_id
),
t2 as (
    # 然后看首次登陆之后的第二天该用户是否还登录
    select a.date, count(b.user_id) as cnt
    from t1 as b
    left join login as a
    on a.user_id = b.user_id and a.date = date_add(b.first_date, interval 1 day)
    where a.date is not null
    group by a.date
),
t3 as (
# 看每一天都有多少新用户登录
    select first_date, count(user_id) as total_people
    from t1
    group by first_date
)
# 计算每一天的次日留存率
select *
from (
    (select t3.first_date as date, (case when total_people is null or t2.cnt is null then 0.000 else round(t2.cnt/total_people, 3) end ) as p
    from t3
    left join t2
    on date_add(t3.first_date,interval 1 day) = t2.date)
    union
    select DISTINCT date, 0.000 as p
    from login
    where date not in (select first_date from t3)
) as e
order by date;