播面

面试题：计算平台的“30天复购率”

1. 题目描述

在电商和用户运营分析中，“复购率”是衡量用户粘性和商品吸引力的核心指标。
请你编写 SQL，计算每日首次购买商品的用户在随后的 30 天内（不含首次购买当天）再次发生购买行为的比例（即 “30天复购率”）。

注：结果按首次购买日期升序排列，复购率保留两位小数（以百分比形式展示，如 33.33%）。

2. 示例数据

订单表：user_orders

3. 期望输出结果

数据解释：

• 2023-10-01 首购用户群（共3人：101, 102, 103）：
  • 用户 101：10-15 再次购买（在 30 天内），算作复购。
  • 用户 102：同天购买两次，但后续 30 天无购买，不算作复购。
  • 用户 103：11-05 再次购买（已过去 35 天，超出 30 天范围），不算作复购。
  • 复购率 = 1 / 3 = 33.33%。
• 2023-10-02 首购用户群（共2人：104, 105）：
  • 用户 104：10-10 再次购买（在 30 天内），算作复购。
  • 用户 105：后续无购买。
  • 复购率 = 1 / 2 = 50.00%。

4. SQL 解答 (标准 MySQL 语法)

WITH user_first_purchase AS (
    -- 步骤 1：找出每个用户的首次购买日期（即首购Cohort）
    SELECT 
        user_id, 
        MIN(purchase_date) AS first_date
    FROM user_orders
    GROUP BY user_id
)
-- 步骤 2：关联原订单表，计算 30 天内的复购情况
SELECT
    fp.first_date AS first_purchase_date,
    COUNT(DISTINCT fp.user_id) AS cohort_size,
    COUNT(DISTINCT CASE 
        WHEN o.purchase_date > fp.first_date 
         AND o.purchase_date <= DATE_ADD(fp.first_date, INTERVAL 30 DAY) 
        THEN fp.user_id 
    END) AS repurchased_users,
    CONCAT(
        ROUND(
            COUNT(DISTINCT CASE 
                WHEN o.purchase_date > fp.first_date 
                 AND o.purchase_date <= DATE_ADD(fp.first_date, INTERVAL 30 DAY) 
                THEN fp.user_id 
            END) * 100.0 / COUNT(DISTINCT fp.user_id), 
            2
        ), 
        '%'
    ) AS repurchase_rate_30d
FROM user_first_purchase fp
LEFT JOIN user_orders o ON fp.user_id = o.user_id
GROUP BY fp.first_date
ORDER BY first_purchase_date;

5. SQL 分析与面试应对技巧

核心考点解析

这道题是经典的留存/复购分析（Cohort Analysis）。面试官主要考察你以下几个能力维度：

1. 子查询与 CTE (公用表表达式)：能否清晰地分离出“用户首次购买时间”这一维度。
2. 日期函数的应用：如何正确处理“首次购买后 30 天内”这一边界条件（避开当天）。
3. LEFT JOIN 的理解：为什么要用 LEFT JOIN 而不是 INNER JOIN？（因为部分首购用户可能根本没有后续购买行为，INNER JOIN 会导致这部分人丢失，从而使分母变小，算出错误的 100% 复购率）。
4. 条件聚合（Conditional Aggregation）：在 COUNT 或 SUM 中嵌入 CASE WHEN 进行条件过滤。

关键步骤拆解

• 第一步 (CTE user_first_purchase)：
  我们通过 MIN(purchase_date) 锁定了每个用户的“首购日”。这一步建立了一个基准线（Cohort）。
• 第二步 (主查询 LEFT JOIN)：
  我们将这个基准线表与原始订单表通过 user_id 进行左关联。
  • 条件 1：o.purchase_date > fp.first_date 确保排除了首购当天的多笔订单。
  • 条件 2：o.purchase_date <= DATE_ADD(fp.first_date, INTERVAL 30 DAY) 严格锁定了 30 天的复购窗口。
• 第三步 (分子与分母的计算)：
  • 分母 (cohort_size)：COUNT(DISTINCT fp.user_id)，代表当天有多少新客。
  • 分子 (repurchased_users)：利用 CASE WHEN 过滤出符合复购时间区间的唯一用户数。

面试避坑指南与加分项

• 细节1：排重（DISTINCT）。一个用户在随后的 30 天内可能买了多次，但在计算复购人数时，他只能被算作 1 个人。因此分子和分母都必须使用 DISTINCT。
• 细节2：排除当天。定义明确指出“不含首次购买当天”，所以在写条件时一定要是 > 而不是 >=。
• 细节3：方言适配（主动提及可加分）。
  • 在 MySQL 中，我们使用 DATE_ADD(date, INTERVAL 30 DAY)。
  • 如果是 Hive / Spark SQL，可以直接写成 date_add(fp.first_date, 30)。
  • 如果是 PostgreSQL，则是 fp.first_date + INTERVAL '30 days'。
  • 在面试中主动和面试官确认底层的数据库系统，或指出不同库的函数差异，会显得你实战经验非常丰富。