03_retention.sql

-- ============================================================
-- 查询03：次日留存率 & 7日留存率
-- 业务问题：3月1日新下单的用户，第2天和第7天还有多少人回来？
--           留存率是判断活动质量的核心指标
-- ============================================================

WITH day0_users AS (
    -- 基准日（第0天）下单的用户，作为分母
    SELECT DISTINCT user_id
    FROM orders
    WHERE order_date = '2025-03-01'
),

day1_users AS (
    -- 次日（第1天）也下单的用户
    SELECT DISTINCT user_id
    FROM orders
    WHERE order_date = '2025-03-02'
),

day7_users AS (
    -- 第7天也下单的用户
    SELECT DISTINCT user_id
    FROM orders
    WHERE order_date = '2025-03-08'
)

SELECT
    COUNT(DISTINCT d0.user_id)                        AS 基准日用户数,

    -- 次日留存：基准日用户中，第二天也下单的比例
    COUNT(DISTINCT d1.user_id)                        AS 次日回访用户数,
    ROUND(
        COUNT(DISTINCT d1.user_id) * 100.0
        / NULLIF(COUNT(DISTINCT d0.user_id), 0),
        1
    )                                                 AS 次日留存率百分比,

    -- 7日留存：基准日用户中，第7天也下单的比例
    COUNT(DISTINCT d7.user_id)                        AS 七日回访用户数,
    ROUND(
        COUNT(DISTINCT d7.user_id) * 100.0
        / NULLIF(COUNT(DISTINCT d0.user_id), 0),
        1
    )                                                 AS 七日留存率百分比

FROM day0_users d0
LEFT JOIN day1_users d1 ON d0.user_id = d1.user_id
-- LEFT JOIN 的关键：保留所有基准日用户
-- 第二天没回来的用户 d1.user_id 为 NULL，COUNT(DISTINCT) 会忽略NULL
-- 这样分母是基准日全量用户，分子是回来的用户，留存率计算正确
LEFT JOIN day7_users d7 ON d0.user_id = d7.user_id;

-- ============================================================
-- 结果解读：
-- 次日留存率行业参考：
--   网约车：15-25%（不是每天都打车）
--   电商：5-15%（低频消费）
--   内容类app：30-50%
--
-- 留存率突然下降 → 可能是活动结束后用户流失（活动拉来的是假增长）
-- 留存率持续上升 → 说明产品体验在改善，用户真正被留住了
--
-- 注意：这里用"下单"作为活跃定义
-- 实际业务中可能用"登录"或"启动app"，取决于产品阶段
-- ============================================================