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

-- 新用户首日登入日期表
with first_date as 
(
    -- 选择每个用户的首次登入日期
    select user_id, min(date) as date
    from login
    group by user_id
),
-- 新用户首次登入后的次日登入日期表
second_date as 
(
    -- 选择首次登入后的次日登入日期
    select distinct login.user_id, login.date
    from first_date 
    join login 
    on first_date.user_id = login.user_id 
    and date_add(first_date.date, interval 1 day) = login.date
)

-- 计算次日留存率，并保留三位小数
select round(count(second_date.date) / count(first_date.date), 3) as retention_rate
from first_date 
left join second_date 
on first_date.user_id = second_date.user_id;