播面

面试题：用户群组月度留存率计算

1. 题目背景与业务场景

在电商、SaaS或移动应用分析中，群组分析（Cohort Analysis）是衡量用户粘性和生命周期价值（LTV）的核心指标。本题要求以用户首次购买月份作为群组划分依据，追踪其在随后第 1、2、3 个月的复购表现（只要该月有至少一次购买行为，即视为留存）。

2. 示例数据准备

表：user_orders（用户订单表）

该表记录了所有用户的历史购买流水。

3. 期望输出结果

根据上述数据计算，预期得到的留存率报表如下：

计算逻辑推导样例（以 2025-01 群组为例）：

• 初始群组人数：2025年1月首购的用户有 101, 102, 103，共 3 人。
• 第 1 个月（2025-02）：只有 101 购买了，留存人数 1人，留存率 $1 / 3 = 33.33\%$。
• 第 2 个月（2025-03）：有 101, 102 购买了，留存人数 2人，留存率 $2 / 3 = 66.67\%$。
• 第 3 个月（2025-04）：只有 101 购买了，留存人数 1人，留存率 $1 / 3 = 33.33\%$。

4. SQL 解答方案（基于标准 SQL / MySQL 8.0）

WITH user_first_purchase AS (
    -- 步骤1：获取每个用户的首次购买日期及所属月份（群组）
    SELECT 
        user_id,
        MIN(order_date) AS first_order_date,
        DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month
    FROM user_orders
    GROUP BY user_id
),

cohort_sizes AS (
    -- 步骤2：计算每个群组的总用户数（分母）
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM user_first_purchase
    GROUP BY cohort_month
),

user_activities AS (
    -- 步骤3：计算用户每次购买行为与首次购买月份的月份差（跨年亦适用）
    SELECT 
        o.user_id,
        fp.cohort_month,
        (YEAR(o.order_date) - YEAR(fp.first_order_date)) * 12 
        + (MONTH(o.order_date) - MONTH(fp.first_order_date)) AS month_diff
    FROM user_orders o
    JOIN user_first_purchase fp ON o.user_id = fp.user_id
)

-- 步骤4：使用 case when 进行条件聚合（Pivot），计算各期留存率
SELECT 
    c.cohort_month AS `首购群组`,
    c.cohort_size AS `群组人数`,
    CONCAT(ROUND(COUNT(DISTINCT CASE WHEN ua.month_diff = 1 THEN ua.user_id END) * 100.0 / c.cohort_size, 2), '%') AS `第1个月留存率`,
    CONCAT(ROUND(COUNT(DISTINCT CASE WHEN ua.month_diff = 2 THEN ua.user_id END) * 100.0 / c.cohort_size, 2), '%') AS `第2个月留存率`,
    CONCAT(ROUND(COUNT(DISTINCT CASE WHEN ua.month_diff = 3 THEN ua.user_id END) * 100.0 / c.cohort_size, 2), '%') AS `第3个月留存率`
FROM cohort_sizes c
LEFT JOIN user_activities ua ON c.cohort_month = ua.cohort_month
GROUP BY c.cohort_month, c.cohort_size
ORDER BY c.cohort_month;

5. 核心考点与 SQL 分析（面试通关秘籍）

面试官在考察这道题时，主要评估候选人以下几个维度的 SQL 功底和业务理解能力：

1. 分治思想与 CTE (公用表表达式) 的运用

• 分析：本题不建议在一个大 SQL 里完成所有逻辑，那会极难阅读。使用 WITH 语法构建清晰的步骤（定位首购 -> 统计基数 -> 计算月差 -> 矩阵透视），能体现你良好的代码规范与工程素养。

2. 精准定位首购（群组基准）

• 分析：留存分析的起点是“首购”。必须先用 MIN(order_date) 锁定每个用户的起点。注意：不能直接用 GROUP BY user_id, DATE_FORMAT(order_date, '%Y-%m')，因为这会得到用户所有的购买月份，而不是“首次”月份。

3. 健壮的月份差值计算 (Month Difference)

• 分析：如何计算 2025-01-15 和 2025-02-10 相差 1 个月？
  • 在 MySQL 中可以使用 TIMESTAMPDIFF(MONTH, date1, date2)。
  • 但更具通用性（兼容各种 SQL 引擎）且不容易出错的写法是：(Year2 - Year1) * 12 + (Month2 - Month1)。这种纯数学计算方式可以规避日、时、分对月份差计算的干扰（比如 1月31日 到 2月1日，在某些基于 30 天算月份差的函数中可能算不出来 1 个月）。

4. 行转列（Pivot）与条件聚合（Conditional Aggregation）

• 分析：最后的输出要求把第 1, 2, 3 个月的留存率作为列展示。这是典型的“行转列”。
  • 利用 CASE WHEN month_diff = X THEN user_id END 配合 COUNT(DISTINCT ...)，能够在同一个 GROUP BY 维度下，分别计算出不同留存周期的活跃人数。
  • 使用 COUNT(DISTINCT ...) 可以自动过滤掉同一个用户在同一个月内多次购买的去重问题。

5. 精度丢失与展示美化

• 分析：
  • 计算留存率时，必须乘以 100.0 （带小数点的浮点数），以防止某些默认整型计算的数据库（如 PostgreSQL 或 SQL Server）直接将 $1/3$ 截断为 $0$。
  • 使用 ROUND(..., 2) 保证精度，并用 CONCAT(..., '%') 转换为业务方更易读的百分比格式。