美团大数据面试SQL-外卖用户复购周期分析
⚠️ 待修正
一、题目背景
这道题来自美团的数据分析岗面试。美团是本地生活服务（外卖、到店、酒旅），数据分析师需要从海量业务数据中挖掘洞见。
业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张用户下单记录表 t10_order_record，记录了每位用户每次下单的时间。请计算每个用户的平均复购周期（即相邻两次下单之间的平均天数）。
平均复购周期 = 所有相邻下单间隔天数的平均值。仅下单1次的用户不计算此指标。
用户下单记录表 t10_order_record：
+----------+----------+---------------------+
| order_id | user_id | order_time |
+----------+----------+---------------------+
| O001 | u01 | 2023-03-01 10:00:00 |
| O002 | u01 | 2023-03-03 10:00:00 |
| O003 | u01 | 2023-03-07 10:00:00 |
| O004 | u01 | 2023-03-12 10:00:00 |
| O005 | u02 | 2023-03-02 12:00:00 |
| O006 | u02 | 2023-03-05 12:00:00 |
| O007 | u02 | 2023-03-10 12:00:00 |
| O008 | u03 | 2023-03-01 18:00:00 |
| O009 | u03 | 2023-03-02 18:00:00 |
| O010 | u04 | 2023-03-03 09:00:00 |
+----------+----------+---------------------+
三、思路分析
复购周期是衡量用户粘性的重要指标，需要计算相邻订单的时间间隔。
解题步骤：
- 使用 LEAD（或LAG）获取每个用户下一次下单时间；
- 计算相邻两次下单的间隔天数
datediff； - 按用户求平均间隔天数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 使用LEAD获取下次下单时间，计算间隔天数
执行SQL
select user_id,
order_id,
order_time,
lead(order_time, 1) over (partition by user_id order by order_time) as next_order_time,
datediff(lead(order_time, 1) over (partition by user_id order by order_time), order_time) as interval_days
from t10_order_record
执行结果
+----------+----------+---------------------+---------------------+---------------+
| user_id | order_id | order_time | next_order_time | interval_days |
+----------+----------+---------------------+---------------------+---------------+
| u01 | O001 | 2023-03-01 10:00:00 | 2023-03-03 10:00:00 | 2 |
| u01 | O002 | 2023-03-03 10:00:00 | 2023-03-07 10:00:00 | 4 |
| u01 | O003 | 2023-03-07 10:00:00 | 2023-03-12 10:00:00 | 5 |
| u01 | O004 | 2023-03-12 10:00:00 | NULL | NULL |
| u02 | O005 | 2023-03-02 12:00:00 | 2023-03-05 12:00:00 | 3 |
| u02 | O006 | 2023-03-05 12:00:00 | 2023-03-10 12:00:00 | 5 |
| u02 | O007 | 2023-03-10 12:00:00 | NULL | NULL |
| u03 | O008 | 2023-03-01 18:00:00 | 2023-03-02 18:00:00 | 1 |
| u03 | O009 | 2023-03-02 18:00:00 | NULL | NULL |
| u04 | O010 | 2023-03-03 09:00:00 | NULL | NULL |
+----------+----------+---------------------+---------------------+---------------+
2. 计算每个用户的平均复购周期
执行SQL
select user_id,
count(1) as order_cnt,
round(avg(interval_days), 1) as avg_repurchase_days
from (
select user_id,
order_id,
order_time,
datediff(
lead(order_time, 1) over (partition by user_id order by order_time),
order_time
) as interval_days
from t10_order_record
) t
where interval_days is not null
group by user_id
order by user_id
执行结果
+----------+-----------+----------------------+
| user_id | order_cnt | avg_repurchase_days |
+----------+-----------+----------------------+
| u01 | 3 | 3.7 |
| u02 | 2 | 4.0 |
| u03 | 1 | 1.0 |
+----------+-----------+----------------------+
u01下单4次（3个间隔），平均每3.7天复购一次，粘性较高；u04仅下单1次，无法计算复购周期。
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
扩展到N单间隔：rn <= 2改为rn <= N，计算到第N次购买的累计间隔
-
按品类/渠道对比：GROUP BY加category/channel，不同品类的复购间隔差异
-
间隔分布分层：0-1天/2-3天/4-7天/8-14天/15+天分段统计用户占比
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t10_order_record (
order_id string COMMENT '订单ID',
user_id string COMMENT '用户ID',
order_time string COMMENT '下单时间'
) COMMENT '用户下单记录表';
-- 数据插入
INSERT INTO t10_order_record VALUES
('O001', 'u01', '2023-03-01 10:00:00'),
('O002', 'u01', '2023-03-03 10:00:00'),
('O003', 'u01', '2023-03-07 10:00:00'),
('O004', 'u01', '2023-03-12 10:00:00'),
('O005', 'u02', '2023-03-02 12:00:00'),
('O006', 'u02', '2023-03-05 12:00:00'),
('O007', 'u02', '2023-03-10 12:00:00'),
('O008', 'u03', '2023-03-01 18:00:00'),
('O009', 'u03', '2023-03-02 18:00:00'),
('O010', 'u04', '2023-03-03 09:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
