阿里巴巴大数据面试SQL-首次到二次购买间隔

⚠️ 待修正

一、题目背景

这道题来自阿里巴巴淘宝事业部的用户增长数据分析岗面试。首单到第二单的间隔是衡量"用户激活速度"的关键指标——如果用户买了一次就再也不来了，说明产品体验或商品吸引力不足。反之，间隔越短说明用户越容易"上瘾"。

业务场景：运营团队在做新用户首单后的push推送时机决策时，需要知道"大多数用户隔多久下第二单"，然后在那个时间窗口内推送优惠券。这道题计算的就是那个时间窗口的基准值。

二、题目

现有一张用户订单表 t10_user_orders，计算每个用户从首次购买到第二次购买的平均间隔天数。

注意：只有下单>=2次的用户才有"前两单间隔"，只下单1次的用户不参与计算。

用户订单表 t10_user_orders：

+----------+----------+---------------------+
| order_id | user_id  |     order_time      |
+----------+----------+---------------------+
| O001     | u01      | 2023-03-01 10:00:00 |
| O002     | u01      | 2023-03-05 10:00:00 |
| O003     | u01      | 2023-03-10 10:00:00 |
| O004     | u02      | 2023-03-02 12:00:00 |
| O005     | u02      | 2023-03-08 12:00:00 |
+----------+----------+---------------------+

三、思路分析

核心是锚定每个用户的前两笔订单：

1. 行号标记：ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time) 为每笔订单编号
2. 提取前两笔：WHERE rn <= 2，同时用 CASE WHEN 把第1笔和第2笔的时间分别放到不同列
3. 计算间隔：DATEDIFF(第2笔日期, 第1笔日期) 得到天数
4. 排除单次用户：HAVING 过滤掉只买了一次的用户

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：用 ROW_NUMBER 为每个用户的订单按时间编号

select user_id, order_time,
       row_number() over (partition by user_id order by order_time) as rn
from t10_user_orders

u01 的3笔订单分别编号为 1、2、3；u02 的2笔订单编号为 1、2。

步骤2：将第1笔和第2笔时间提取到同一行

用 CASE WHEN 将 rn=1 和 rn=2 的时间分别放入 first_order 和 second_order 列，外层 GROUP BY user_id 用 MAX 聚合（因为每列只有一个非NULL值）。

执行SQL

select user_id,
       datediff(max(second_order), max(first_order)) as interval_days
from (
    select user_id,
           case when rn = 1 then order_time end as first_order,
           case when rn = 2 then order_time end as second_order
    from (
        select user_id, order_time,
               row_number() over (partition by user_id order by order_time) as rn
        from t10_user_orders
    ) t1
    where rn <= 2
) t2
group by user_id having interval_days is not null

执行结果

+----------+---------------+
| user_id  | interval_days |
+----------+---------------+
| u01      | 4             |
| u02      | 6             |
+----------+---------------+

u01 从 03-01 首次购买到 03-05 第二次购买间隔 4 天。u02 从 03-02 到 03-08 间隔 6 天。两位用户的平均复购间隔 = (4+6)/2 = 5 天。

五、常见坑点

坑1：CASE WHEN + MAX 的技巧理解

有人会疑惑为什么用 MAX() 聚合——因为同一用户的前两笔数据分布在两行，CASE WHEN 让每行只有一个非NULL的日期列，MAX() 在此处的实际作用是"取非NULL值"（因为 NULL < 任何日期值）。

坑2：LEAD 方案需要额外处理

另一种解法是 LEAD(order_time, 1) OVER (PARTITION BY user_id ORDER BY order_time) 获取下一单时间，但 LEAD 让每笔订单都能看到下一笔的时间，你需要额外取 rn=1 那一行才是"首单的下一单"。ROW_NUMBER + CASE WHEN 方案更直观。

坑3：只买了一次的用户

如果用户只下了一单，rn=2 不存在，CASE WHEN 返回 NULL，最终 DATEDIFF(NULL, *) = NULL。需要用 HAVING interval_days IS NOT NULL 或最外层过滤掉这些用户。

六、举一反三

1. LEAD 替代方案：LEAD(order_time) OVER (PARTITION BY user_id ORDER BY order_time) 直接获取下一单时间，配合 WHERE rn=1 取首单的下一单间隔
2. 扩展到N单间隔：把 rn <= 2 改成 rn <= N，计算"首次购买到第N次购买"的累计间隔和平均每单间隔
3. 按渠道对比：GROUP BY 加入 channel 字段，对比不同渠道来源的用户首单到二单间隔是否有显著差异
4. 间隔分布分析：将 interval_days 分段（0-1天/2-3天/4-7天/8-14天/15+天），用 CASE WHEN 分类统计各区间用户数占比

七、知识点总结

考点	说明
ROW_NUMBER + WHERE rn <= 2	精确取每个用户的前两笔订单
CASE WHEN + MAX 列转行	将两行的日期合并到同一行，便于计算差值
DATEDIFF	计算两个日期的天数差
HAVING IS NOT NULL	过滤只买了一单的用户

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t10_user_orders (
    order_id   string COMMENT '订单ID',
    user_id    string COMMENT '用户ID',
    order_time string COMMENT '下单时间'
) COMMENT '用户订单表';

INSERT INTO t10_user_orders VALUES
('O001','u01','2023-03-01 10:00:00'),('O002','u01','2023-03-05 10:00:00'),
('O003','u01','2023-03-10 10:00:00'),('O004','u02','2023-03-02 12:00:00'),
('O005','u02','2023-03-08 12:00:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题