字节跳动大数据面试SQL-用户复购率计算
一、题目背景
这道题来自字节跳动电商业务的数据分析岗面试。复购率是衡量用户忠诚度的核心指标——用户买了一次之后，还会再买吗？对于抖音电商这样的平台，复购率直接关系到 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()按用户分区、按时间排序，标记每笔订单是该用户的第几单 - rn = 1 的是首单，rn > 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)(https://www.dwsql.com)
步骤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)(https://www.dwsql.com)
五、常见坑点
坑1：一个月内多次购买的计数
用户在同一个自然月内可能有多笔订单（如用户1在1月有2单）。ROW_NUMBER 会正确标记第2单为复购。但如果用户在这个月的第一单恰好是他在平台的首次购买，那只有第2单及之后才算复购。
坑2：复购率 ≠ 留存率
复购率和留存率经常被混淆。留存率看的是"某天/月新增的用户，在后一天/月是否还活跃"（按人维度，只看是否回来）。复购率看的是"某月所有下单用户中，有多少是回头客"（按订单维度）。
六、举一反三
- 单品复购率：加上
product_id维度，统计"用户对同一个商品的复购率"——这需要PARTITION BY user_id, product_id - 复购周期：统计用户两次购买之间的平均间隔天数，用
LAG(order_date)计算
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER | 按用户+时间排序，标记第N单 |
| rn > 1 | 判断是否为复购（非首次） |
| DATE_FORMAT | 提取月份用于 GROUP BY |
| 条件聚合 | SUM(CASE WHEN) 统计复购用户数 |
八、建表语句和数据插入
点击展开 DDL & DML
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');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
