字节跳动大数据面试SQL用户复购率计算

这道SQL题目源自字节跳动电商业务的数据分析岗位面试，主要考察复购率的计算——一个看似基础却极易踩坑的经典业务场景。复购率直接体现了用户的忠诚度，对于抖音电商这类平台而言，更是评估用户生命周期价值（LTV）的核心指标。接下来，我们将逐步拆解这个题目，带你掌握解题思路。

一、题目背景

复购率是衡量用户忠诚度的关键指标——用户完成首次购买后，是否还会再次下单？在抖音电商这类场景下，复购率直接关联到LTV（用户生命周期价值），是评估平台粘性与用户长期贡献的重要依据。

二、题目要求

现有订单表 t16_zj_orders，记录了每位用户的每笔订单信息。请按月计算复购率。

t16_zj_orders 表结构

 ----------- ---------- -------------
| order_id| user_id| order_date|
 ----------- ---------- -------------
| 1       | 1      | 2025-01-05|
| 2       | 2      | 2025-01-10|
| 3       | 1      | 2025-01-15|
| 4       | 3      | 2025-01-20|
| 5       | 1      | 2025-02-03|
| 6       | 2      | 2025-02-10|
| 7       | 4      | 2025-02-15|
| 8       | 3      | 2025-02-20|
| 9       | 1      | 2025-03-05|
| 10      | 2      | 2025-03-10|
 ----------- ---------- -------------

关键定义：

• 首单：用户在整个平台的第一笔订单
• 复购单：用户在首单之后产生的任何订单，均视为复购单
• 总下单用户数：某个月内至少下过一单的用户数量
• 复购用户数：某个月内下过单，且在该月之前的任何时间也下过单的用户数量
• 复购率 = 当月复购用户数 / 当月总下单用户数

三、解题思路

本题的核心在于判断“该用户是否首次在平台下单”：

• 使用 ROW_NUMBER() 按用户分区、按下单时间升序排列，为每笔订单生成序号
• 序号为1的订单为首单，序号大于1的为复购单
• 按月聚合：用复购用户数除以总下单用户数，得到当月的复购率

四、逐步推导

步骤1：为每个订单标记序号，区分首单与复购单

Spark SQL 实现

SELECT
 user_id,
 order_id,
 order_date,
 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn,
 CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) = 1 THEN 0 ELSE 1 END AS is_repurchase --0: 首单，1: 复购单
FROM t16_zj_orders;

执行结果

 ---------- ----------- ------------- ----- ----------------
| user_id| order_id| order_date  | rn  | is_repurchase|
 ---------- ----------- ------------- ----- ----------------
| 1      | 1        | 2025-01-05  | 1   | 0            |
| 1      | 3        | 2025-01-15  | 2   | 1            |
| 1      | 5        | 2025-02-03  | 3   | 1            |
| 1      | 9        | 2025-03-05  | 4   | 1            |
| 2      | 2        | 2025-01-10  | 1   | 0            |
| 2      | 6        | 2025-02-10  | 2   | 1            |
| 2      | 10       | 2025-03-10  | 3   | 1            |
| 3      | 4        | 2025-01-20  | 1   | 0            |
| 3      | 8        | 2025-02-20  | 2   | 1            |
| 4      | 7        | 2025-02-15  | 1   | 0            |
 ---------- ----------- ------------- ----- ----------------
10 rows selected (0.352 seconds)

步骤2：按月聚合，计算复购率

SELECT
 month,
 COUNT(DISTINCT user_id) AS total_users,
 COUNT(DISTINCT CASE WHEN is_repurchase = 1 THEN user_id END) AS repurchase_users,
 ROUND(COUNT(DISTINCT CASE WHEN is_repurchase = 1 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repurchase_rate
FROM (
 SELECT 
  DATE_FORMAT(order_date, 'yyyy-MM') AS month,
  user_id,
  CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) > 1 THEN 1 ELSE 0 END AS is_repurchase
 FROM t16_zj_orders
) t
GROUP BY month
ORDER BY month

最终结果：

 ---------- -------------- ------------------- ------------------
| month    | total_users | repurchase_users | repurchase_rate |
 ---------- -------------- ------------------- ------------------
| 2025-01  | 3           | 1                | 33.33           |
| 2025-02  | 4           | 3                | 75.00           |
| 2025-03  | 2           | 2                | 100.00          |
 ---------- -------------- ------------------- ------------------
3 rows selected (10.109 seconds)

五、常见易错点

易错1：一个月内多次购买的计数问题

同一用户在一个自然月内可能产生多笔订单（如用户1在1月就有2单）。借助 ROW_NUMBER 能正确识别第二单为复购单。但要注意：如果用户当月的第一单恰好是其在平台的首单，那么该月内只有第二单及以后才计入复购。换句话说，首单当月也可能出现复购——只要用户在该月内购买了第二次。

易错2：复购率 ≠ 留存率

这两个指标经常被混淆。留存率关注的是“某天或某月新增的用户，在未来某日或某月是否依然活跃”（按用户维度，只看是否回来）。而复购率关注的是“某月所有下单用户中，有多少人曾经下过单”（按订单维度，判断是否为回头客）。举个例子：某月只有2个用户下单，其中1个是新用户且仅买了一次，另1个是老用户买了10次。按照留存率，新用户下月可能流失，留存率低；按复购率，复购用户数为1，总下单用户数为2，复购率为50%。两者衡量的逻辑完全不同，面试时务必区分清楚。

六、举一反三

• 单品复购率：增加 product_id 维度，统计“用户对同一商品的复购率”——需要 PARTITION BY user_id, product_id 重新排序。
• 复购周期：使用 LAG(order_date) 计算用户两次购买之间的平均间隔天数，可进一步分析用户粘性与回购行为。

七、知识点总结

八、建表语句与数据插入

CREATE TABLE IF NOT EXISTS t16_zj_orders (
 order_id INT,
 user_id INT,
 order_date STRING
);
INSERT INTO t16_zj_orders VALUES
(1, 1, '2025-01-05'),
(2, 2, '2025-01-10'),
(3, 1, '2025-01-15'),
(4, 3, '2025-01-20'),
(5, 1, '2025-02-03'),
(6, 2, '2025-02-10'),
(7, 4, '2025-02-15'),
(8, 3, '2025-02-20'),
(9, 1, '2025-03-05'),
(10, 2, '2025-03-10');