阿里巴巴大数据面试SQL-购物车加购转化率
一、题目背景
淘宝每天有数亿次加购行为，但从"加入购物车"到"真正下单付款"之间存在巨大的流失——超过50%的加购商品最终没有被购买。运营团队需要知道用户在加购后多久内最可能下单，以便在最佳时间窗口内推送优惠券或限时折扣提醒。
业务场景：淘宝APP的"购物车降价提醒"和"库存紧张提示"功能，背后就是加购转化分析。数据分析师计算出"加购后1小时内的下单转化率最高"后，产品经理设计在加购后30分钟推送一张限时优惠券——这就是数据驱动产品决策的典型场景。
二、题目
现有加购行为表 t13_cart_log 和下单表 t13_order_log。请统计加购商品在 24小时内、3天内、7天内 的下单转化率，以及各时间窗口的转化人数和占比。
加购行为表 t13_cart_log：
+----------+----------+----------+---------------------+
| user_id | item_id | cart_id | cart_time |
+----------+----------+----------+---------------------+
| u01 | I1001 | C001 | 2023-03-01 10:00:00 |
| u01 | I1002 | C002 | 2023-03-01 10:30:00 |
| u02 | I1001 | C003 | 2023-03-01 11:00:00 |
| u02 | I1003 | C004 | 2023-03-01 12:00:00 |
| u03 | I1001 | C005 | 2023-03-02 09:00:00 |
| u03 | I1002 | C006 | 2023-03-02 10:00:00 |
| u01 | I1004 | C007 | 2023-03-03 08:00:00 |
| u04 | I1001 | C008 | 2023-03-03 15:00:00 |
+----------+----------+----------+---------------------+
下单表 t13_order_log：
+----------+----------+----------+---------------------+
| order_id | user_id | item_id | order_time |
+----------+----------+----------+---------------------+
| O001 | u01 | I1001 | 2023-03-01 10:15:00 |
| O002 | u02 | I1001 | 2023-03-01 13:00:00 |
| O003 | u01 | I1002 | 2023-03-03 14:00:00 |
| O004 | u03 | I1001 | 2023-03-04 09:00:00 |
| O005 | u04 | I1001 | 2023-03-04 10:00:00 |
+----------+----------+----------+---------------------+
注意：同一用户对同一商品可能多次加购，每次加购独立统计转化。下单的 user_id + item_id 与加购记录匹配。
三、思路分析
核心是加购表为主表 + LEFT JOIN下单表 + 时间窗口分类 + COUNT DISTINCT：
- 关联匹配：加购表 LEFT JOIN 下单表，关联条件为同一用户、同一商品，且下单时间在加购之后
- 时间差计算：
unix_timestamp(order_time) - unix_timestamp(cart_time)得到秒数，除以3600得小时数 - 窗口分类：CASE WHEN 分为 ≤24h / ≤3d(72h) / ≤7d(168h) / >7d(未转化)
- 转化率计算：各窗口的转化人数 / 总加购人数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：加购表 LEFT JOIN 下单表，计算时间差
select c.user_id, c.item_id, c.cart_time,
o.order_time,
(unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600 as diff_hours
from t13_cart_log c
left join t13_order_log o
on c.user_id = o.user_id and c.item_id = o.item_id
and o.order_time >= c.cart_time
步骤2：按加购记录标记转化窗口
select c.cart_id, c.user_id, c.item_id,
case
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 24 then '24h内转化'
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 72 then '3天内转化'
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 168 then '7天内转化'
else '未转化'
end as conversion_window
from t13_cart_log c
left join t13_order_log o
on c.user_id = o.user_id and c.item_id = o.item_id
and o.order_time >= c.cart_time
group by c.cart_id, c.user_id, c.item_id
用
MIN()取最早一次下单时间——加购后可能多次购买同一商品，取最早那次衡量转化速度。
步骤3：统计各窗口转化率
执行SQL
select conversion_window,
count(distinct cart_id) as cart_cnt,
round(count(distinct cart_id) * 100.0 / sum(count(distinct cart_id)) over (), 2) as pct
from (
select c.cart_id, c.user_id, c.item_id,
case
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 24 then '24h内转化'
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 72 then '3天内转化'
when min((unix_timestamp(o.order_time) - unix_timestamp(c.cart_time)) / 3600) <= 168 then '7天内转化'
else '未转化'
end as conversion_window
from t13_cart_log c
left join t13_order_log o
on c.user_id = o.user_id and c.item_id = o.item_id
and o.order_time >= c.cart_time
group by c.cart_id, c.user_id, c.item_id
) t
group by conversion_window
order by field(conversion_window, '24h内转化', '3天内转化', '7天内转化', '未转化')
执行结果
+-----------------+----------+-------+
| conversion_window | cart_cnt | pct |
+-----------------+----------+-------+
| 24h内转化 | 2 | 25.00 |
| 3天内转化 | 1 | 12.50 |
| 7天内转化 | 2 | 25.00 |
| 未转化 | 3 | 37.50 |
+-----------------+----------+-------+
24h内转化率25%（C001→O001、C003→O002），7天内累计转化率62.5%。实际淘宝加购整体转化率约25%-40%在7天内完成。
五、常见坑点
坑1：一个加购可能对应多次下单
同一用户加购后可能在24h内买一次、7天内又买一次。用 MIN(order_time) 取最早下单时间来判断"首次转化在哪个窗口"，符合业务直觉（首单转化速度）。
坑2：下单时间 < 加购时间的情况
如果用户先下单再加购（即数据时序异常），order_time >= cart_time 条件会自然排除。但实际中可能存在"先买后加购"的合法场景（如退款重买），需要确认业务口径。
坑3：分母用加购人数 vs 加购次数
用 COUNT(DISTINCT user_id) 算的是"加购用户中有多少转化了"，用 COUNT(*) 算的是"加购事件中有多少转化了"。本题以加购事件为粒度更细，能反映每次加购的转化效率。
六、举一反三
- 按品类拆分转化率：关联商品品类表，GROUP BY 加
category_id，对比各品类的加购转化率差异 - 加购但未转化原因：对"未转化"的用户关联近期浏览/收藏行为，判断是"还在犹豫"还是"已放弃"
- 最优推送时机：按小时粒度统计"加购后第N小时转化"，画转化时间分布直方图，找到转化高峰窗口
- 加购商品降价提醒：关联商品价格表，监控加购后商品降价 >10% 的用户，推送降价通知推动转化
七、知识点总结
| 考点 | 说明 |
|---|---|
| LEFT JOIN + 时间条件 | 加购表为主，关联后续下单记录 |
| unix_timestamp 时间差 | 秒数差 ÷ 3600 = 小时数 |
| CASE WHEN 时间窗口分类 | ≤24h / ≤72h / ≤168h 三级窗口 |
| MIN 取最早转化 | 多次下单取最早一次衡量首单速度 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t13_cart_log (
user_id string COMMENT '用户ID',
item_id string COMMENT '商品ID',
cart_id string COMMENT '加购记录ID',
cart_time string COMMENT '加购时间'
) COMMENT '加购行为表';
CREATE TABLE t13_order_log (
order_id string COMMENT '订单ID',
user_id string COMMENT '用户ID',
item_id string COMMENT '商品ID',
order_time string COMMENT '下单时间'
) COMMENT '下单表';
INSERT INTO t13_cart_log VALUES
('u01', 'I1001', 'C001', '2023-03-01 10:00:00'),
('u01', 'I1002', 'C002', '2023-03-01 10:30:00'),
('u02', 'I1001', 'C003', '2023-03-01 11:00:00'),
('u02', 'I1003', 'C004', '2023-03-01 12:00:00'),
('u03', 'I1001', 'C005', '2023-03-02 09:00:00'),
('u03', 'I1002', 'C006', '2023-03-02 10:00:00'),
('u01', 'I1004', 'C007', '2023-03-03 08:00:00'),
('u04', 'I1001', 'C008', '2023-03-03 15:00:00');
INSERT INTO t13_order_log VALUES
('O001', 'u01', 'I1001', '2023-03-01 10:15:00'),
('O002', 'u02', 'I1001', '2023-03-01 13:00:00'),
('O003', 'u01', 'I1002', '2023-03-03 14:00:00'),
('O004', 'u03', 'I1001', '2023-03-04 09:00:00'),
('O005', 'u04', 'I1001', '2023-03-04 10:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
