播面

面试真题：用户首购留存与 LTV（生命周期价值）分析

题目背景：
在电商和 SaaS 业务中，同类群组分析（Cohort Analysis）是评估用户质量和计算 LTV 的核心方法。本题要求根据用户的首次购买月份（即首购月）对用户进行分组（Cohort），并计算这些用户在首购当月（第 0 个月）及后续 12 个月内（第 1 到第 12 个月）的累计贡献 GMV。

1. 示例数据

订单表：orders

2. 期望输出

根据上述数据，2023-01 首购的用户有 101 和 102；2023-02 首购的用户有 103。
计算得出的各首购群组在后续各相对月份的累计 GMV 如下表所示（注：101 在 2024-03-01 的订单超出了 12 个月的范围，需过滤）：

3. SQL 解决方案 (以 Standard SQL/Hive/MySQL 8.0+ 为准)

WITH user_first_purchase AS (
    -- Step 1: 计算每个用户的首购日期和首购月份
    SELECT 
        user_id,
        MIN(order_date) AS first_order_date,
        DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS cohort_month
    FROM orders
    GROUP BY user_id
),
order_month_diff AS (
    -- Step 2: 关联原订单表，计算每笔订单相对于首购月的月份差
    SELECT 
        o.user_id,
        uf.cohort_month,
        o.order_date,
        o.gmv,
        -- 计算两个日期之间相差的月份
        (YEAR(o.order_date) - YEAR(uf.first_order_date)) * 12 + 
        (MONTH(o.order_date) - MONTH(uf.first_order_date)) AS relative_month
    FROM orders o
    JOIN user_first_purchase uf ON o.user_id = uf.user_id
),
cohort_monthly_agg AS (
    -- Step 3: 过滤 12 个月内的数据，并按首购月和相对月份汇总单月 GMV
    SELECT 
        cohort_month,
        relative_month,
        SUM(gmv) AS monthly_gmv
    FROM order_month_diff
    WHERE relative_month BETWEEN 0 AND 12
    GROUP BY cohort_month, relative_month
)
-- Step 4: 运用窗口函数计算累计 GMV
SELECT 
    cohort_month,
    relative_month,
    monthly_gmv,
    SUM(monthly_gmv) OVER(
        PARTITION BY cohort_month 
        ORDER BY relative_month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_gmv
FROM cohort_monthly_agg
ORDER BY cohort_month, relative_month;

4. 核心考点与解题思路剖析

此题是互联网大厂（如字节跳动、美团、拼多多）数据分析师与数据研发岗位高频出现的实战场景题，主要考察候选人处理时间序列数据、用户群组细分以及复杂窗口函数的能力。

剖析一：确定“首购月”（Cohort）

• 思路：要分析“首购”，必须先找到每个用户在全表中的生命起点。使用 MIN(order_date) 锁定每个用户的首次下单时间，并将其转化为月份粒度（如 2023-01-01），作为该用户所属的 Cohort 标签。
• 面试加分点：在实际数仓中，为了性能，面试官会期望你问一句：“我们是否有现成的用户维表直接记录了 first_buy_date？”。如果有，直接关联维表可以避免在大表上执行高昂的 GROUP BY 操作。

剖析二：计算“相对月份差”（Relative Month）

• 思路：
  • 计算相对时间是 Cohort 分析的灵魂。
  • 在 MySQL / Hive 中，日期差计算常用：(YEAR(t2) - YEAR(t1)) * 12 + (MONTH(t2) - MONTH(t1))。
  • 如果是在 Spark SQL 或 PostgreSQL 中，可以使用 MONTHS_BETWEEN 或 AGE 函数。
• 易错点：注意边界过滤。题目要求“后续 12 个月内”（即第 0 个月到第 12 个月，共 13 个月）。在 WHERE 条件中必须限制 relative_month BETWEEN 0 AND 12。超出 12 个月的消费（如示例中 101 在 2024-03 的订单）应当被剔除。

剖析三：利用窗口函数计算“累计值”

• 思路：
  • 在 Step 3 完成了群组在各相对月份的非累计 GMV 汇总。
  • 在 Step 4 中，使用窗口函数 SUM(monthly_gmv) OVER (PARTITION BY cohort_month ORDER BY relative_month) 来实现滚动累加（Running Total）。
• 技术细节：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 明确定义了累加范围是从当前群组的起点到当前行，保证了计算的准确性和可读性。

进阶追问：如何处理“缺失月份”的填充？

• 面试官追问：如果某个首购月组在第 3 个月没有任何人消费（例如 2023-02 组没有 relative_month = 1 的数据），上面的 SQL 结果会直接跳过这一月。如果要求输出必须包含 0, 1, 2 ... 12 完整的 13 行记录，且未消费月份的累计 GMV 承接上个月的值，该怎么写？
• 应对策略：
  1. 使用一个生成 0-12 序列的临时表/常数表，与 user_first_purchase 的唯一 cohort_month 进行 CROSS JOIN（笛卡尔积），生成所有可能的 (cohort_month, relative_month) 组合。
  2. 用该组合表 LEFT JOIN 实际的消费汇总数据。
  3. 使用 COALESCE(monthly_gmv, 0) 将缺失月的消费填补为 0。
  4. 再次执行窗口函数累加，即可实现无缝的 LTV 趋势展现。