大厂SQL面试题：计算用户“30天复购率”

1. 题目背景与定义

在电商和零售行业中，“复购率”是衡量用户粘性和商品吸引力的核心指标。
30天复购率定义为：在某天首次购买商品的用户中，在随后的 30 天内（不含首次购买当天）再次发生购买行为的用户比例。

请编写 SQL 计算每日新增用户（即当天首次购买的用户）在随后的 30天复购率。

2. 示例数据

订单表：orders

期望输出结果

结果解析：

• 2023-01-01：有 3 个新用户（101, 102, 103）。
  • 101 在 01-15 再次购买（相隔 14 天，符合条件）；
  • 102 在 02-05 再次购买（相隔 35 天，超出 30 天范围）；
  • 103 无后续购买。
  • 复购率 = 1 / 3 = 33.33%。
• 2023-01-02：有 2 个新用户（104, 105）。
  • 104 在当天有两笔订单，但 01-10 又购买了一次（相隔 8 天，符合条件）；
  • 105 无后续购买。
  • 复购率 = 1 / 2 = 50.00%。

3. SQL 解答方案 (标准 SQL / MySQL)

WITH user_first_purchase AS (
    -- Step 1: 找出每个用户的首次购买日期（Cohort Date）
    SELECT 
        user_id, 
        MIN(order_date) AS first_buy_date
    FROM orders
    GROUP BY user_id
),
user_repurchase_flag AS (
    -- Step 2: 关联原始订单表，判断用户在首次购买后的 1 到 30 天内是否有购买
    SELECT 
        f.user_id,
        f.first_buy_date,
        -- 如果存在满足区间条件的订单，标记为 1，否则为 0
        MAX(CASE WHEN o.order_date > f.first_buy_date 
                  AND o.order_date <= DATE_ADD(f.first_buy_date, INTERVAL 30 DAY) 
                 THEN 1 ELSE 0 END) AS is_repurchased
    FROM user_first_purchase f
    LEFT JOIN orders o ON f.user_id = o.user_id
    GROUP BY f.user_id, f.first_buy_date
)
-- Step 3: 按首次购买日期分组，计算每日新增人数、复购人数和复购率
SELECT 
    first_buy_date AS cohort_date,
    COUNT(DISTINCT user_id) AS new_users_count,
    SUM(is_repurchased) AS repurchased_users_count,
    ROUND(SUM(is_repurchased) * 1.0 / COUNT(DISTINCT user_id), 4) AS repurchase_rate_30d
FROM user_repurchase_flag
GROUP BY first_buy_date
ORDER BY cohort_date;

4. 面试官视角：SQL 深度分析与考点

这是一道典型的留存分析（Cohort Analysis）题目。面试官在考察这道题时，主要侧重以下几个维度：

核心考点 1：如何定义“首次购买”

很多候选人会直接拿当天的所有购买用户当做分母，这是错误的。题目要求是“在某天首次购买商品的用户中”。

• 应对策略：必须先使用 GROUP BY user_id 配合 MIN(order_date)，锁定每个用户唯一的“人生第一次购买日期”（Cohort Date），以此作为后续统计的基准。

核心考点 2：复购时间窗口的边界处理

题目要求：“在随后的 30 天内（不含首次购买当天）”。

• 边界条件：
  • 下界：order_date > first_buy_date（排除当天。如示例中 104 号用户在首日购买了多次，这些首日订单不能算作复购）。
  • 上界：order_date <= first_buy_date + 30天。
• 函数选择：在 MySQL 中使用 DATE_ADD(first_buy_date, INTERVAL 30 DAY)，或者使用 DATEDIFF(order_date, first_buy_date) BETWEEN 1 AND 30。面试时注意解释不同数据库的日期函数差异（如 Hive 里的 date_add(first_buy_date, 30)）。

核心考点 3：去重与性能优化（避免数据膨胀）

如果一个用户在 30 天内多次复购，直接 LEFT JOIN 会导致数据膨胀。

• 优化策略：在 user_repurchase_flag 临时表中，我们使用了 MAX(CASE WHEN ... THEN 1 ELSE 0 END) 并对 user_id 进行 GROUP BY。这样可以保证每个用户在结果集中只保留一条记录，复购了多次也只被记为 1。这不仅简化了最后的聚合计算，还极大地提高了 SQL 的执行效率，避免了多对多关联导致的内存溢出（OOM）。