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

WITH 
-- 获取每个用户的最早活跃日期作为其首次出现日期
first_occurrence AS (
    SELECT 
        uid,
        DATE(MIN(in_time)) AS first_dt
    FROM tb_user_log
    GROUP BY uid
),

-- 获取每个用户每天活跃的记录（处理跨天情况）
user_active_dates AS (
    SELECT DISTINCT 
        uid,
        DATE(in_time) AS active_date
    FROM tb_user_log
    UNION
    SELECT DISTINCT 
        uid,
        DATE(out_time) AS active_date
    FROM tb_user_log
),

-- 为每个用户按日期排序，并使用LEAD获取下一天的活跃状态
user_activity_sequence AS (
    SELECT 
        uid,
        active_date,
        LEAD(active_date) OVER (PARTITION BY uid ORDER BY active_date) AS next_active_date
    FROM user_active_dates
),

-- 计算每天的新用户数及其次日留存情况
daily_stats AS (
    SELECT 
        fo.first_dt AS dt,
        COUNT(DISTINCT fo.uid) AS new_users,
        COUNT(DISTINCT CASE 
            WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid 
        END) AS retained_users
    FROM first_occurrence fo
    LEFT JOIN user_activity_sequence uas ON fo.uid = uas.uid AND fo.first_dt = uas.active_date
    WHERE fo.first_dt BETWEEN '2021-11-01' AND '2021-11-30'
    GROUP BY fo.first_dt
)

-- 计算并格式化留存率
SELECT 
    dt,
    ROUND(IF(new_users = 0, 0, retained_users / new_users), 2) AS uv_left_rate
FROM daily_stats
ORDER BY dt;