题解 | 2021年11月每天新用户的次日留存率

WITH all_active AS ( -- 这里将数据连起来其实是因为有坑，可能以为人晚上看，结束的时候已经是第二天凌晨了
    -- 拆成两条记录，取 in_time/out_time 的日期，并去重
    SELECT DISTINCT uid, DATE(in_time) AS active_day
    FROM tb_user_log
    WHERE in_time IS NOT NULL
    UNION
    SELECT DISTINCT uid, DATE(out_time) AS active_day
    FROM tb_user_log
    WHERE out_time IS NOT NULL
) ,
xia As ( --这里是查询下一天的阅读日期
    SELECt 
        uid,
        active_day,
        LEAD(active_day) OVER(PARTITION BY uid ORDER BY active_day ASC) AS next_time
    FROM all_active
)
,
new_user_daily AS ( --这里查询第一次登陆的时间
    SELECT 
        MIN(active_day) AS dt,
        uid
    FROM all_active
    GROUP BY uid
    HAVING MONTH(dt) = 11
),
daily_retention AS ( --这里进行表链接，按首次登陆时间和uid进行链接
    SELECT 
        n.dt,
        COUNT(DISTINCT n.uid) AS new_user_count,
        COUNT(DISTINCT CASE 
            WHEN DATE_ADD(n.dt, INTERVAL 1 DAY) = a.next_time THEN n.uid 
        END) AS retained_next_day
    FROM new_user_daily n
    LEFT JOIN xia a
        ON n.uid = a.uid
        AND n.dt = a.active_day
    GROUP BY n.dt
)
SELECT 
    DATE_FORMAT(dt, '%Y-%m-%d') AS dt,
    ROUND(retained_next_day * 1.0 / new_user_count, 2) AS uv_left_rate
FROM daily_retention
WHERE new_user_count > 0
ORDER BY dt;