字节跳动大数据面试SQL-用户行为漏斗分析
一、题目背景
这道题来自字节跳动电商业务的数据分析岗面试。用户在抖音电商的典型路径是：浏览商品 → 加入购物车 → 提交订单 → 完成支付。每一步都会有用户流失，产品经理需要通过"漏斗分析"定位流失最严重的环节。
业务场景：如果浏览到加购的转化率很低（比如只有10%），说明商品详情页不够吸引人；如果支付转化率很低，可能是支付流程太复杂。漏斗分析就是"用数据定位问题"。
二、题目
现有四张用户行为表，分别记录了浏览、加购、下单、支付四个环节。请统计整体的漏斗转化率。
表结构：
t11_zj_view (user_id, product_id, view_time) -- 浏览
t11_zj_cart (user_id, product_id, cart_time) -- 加购
t11_zj_order (user_id, product_id, order_time) -- 下单
t11_zj_pay (user_id, product_id, pay_time) -- 支付
具体样例数据（时间通过函数生成，模拟真实用户行为链路）：
-- t11_zj_view 表数据
+----------+-------------+----------------------+
| user_id | product_id | view_time |
+----------+-------------+----------------------+
| 1 | A | 2024-01-10 08:00:00 |
| 1 | B | 2024-01-10 08:05:00 |
| 2 | A | 2024-01-10 09:00:00 |
| 2 | C | 2024-01-10 09:30:00 |
| 3 | A | 2024-01-10 10:00:00 |
| 3 | B | 2024-01-10 10:30:00 |
| 4 | A | 2024-01-10 14:00:00 |
| 5 | A | 2024-01-10 16:00:00 |
+----------+-------------+----------------------+
-- t11_zj_cart 表数据
+----------+-------------+----------------------+
| user_id | product_id | cart_time |
+----------+-------------+----------------------+
| 1 | A | 2024-01-10 08:10:00 |
| 2 | A | 2024-01-10 09:35:00 |
| 3 | A | 2024-01-10 10:35:00 |
| 3 | B | 2024-01-10 10:36:00 |
| 4 | A | 2024-01-10 14:30:00 |
+----------+-------------+----------------------+
-- t11_zj_order
+----------+-------------+----------------------+
| user_id | product_id | order_time |
+----------+-------------+----------------------+
| 1 | A | 2024-01-10 08:20:00 |
| 2 | A | 2024-01-10 09:40:00 |
| 3 | A | 2024-01-10 10:40:00 |
| 4 | A | 2024-01-10 14:50:00 |
+----------+-------------+----------------------+
-- t11_zj_pay 表数据
+----------+-------------+----------------------+
| user_id | product_id | pay_time |
+----------+-------------+----------------------+
| 1 | A | 2024-01-10 08:25:00 |
| 2 | A | 2024-01-10 09:45:00 |
| 4 | A | 2024-01-10 15:00:00 |
+----------+-------------+----------------------+
用户漏斗行为链路：
-- 用户1：完整漏斗（浏览A→加购A→下单A→支付A），另浏览了B但未加购
-- 用户2：完整漏斗（浏览A→加购A→下单A→支付A），另浏览了C但未加购
-- 用户3：浏览A、B并加购，完成下单A，但最终未支付（下单→支付流失）
-- 用户4：完整漏斗（浏览A→加购A→下单A→支付A）
-- 用户5：仅浏览A，未加购（浏览→加购流失）
期望输出：每个环节的独立用户数，以及相对于上一环节的转化率。
三、思路分析
漏斗分析的本质是"逐级 COUNT DISTINCT"：
- 每个环节 COUNT DISTINCT user_id
- 转化率 = 本环节人数 / 上一环节人数
- 用 LAG 窗口函数或直接子查询获取上一环节人数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：各环节独立去重统计
Spark SQL
SELECT '浏览' AS step, COUNT(DISTINCT user_id) AS users FROM t11_zj_view
UNION ALL
SELECT '加购', COUNT(DISTINCT user_id) FROM t11_zj_cart
UNION ALL
SELECT '下单', COUNT(DISTINCT user_id) FROM t11_zj_order
UNION ALL
SELECT '支付', COUNT(DISTINCT user_id) FROM t11_zj_pay;
结果：
+-------+--------+
| step | users |
+-------+--------+
| 浏览 | 5 |
| 加购 | 4 |
| 下单 | 4 |
| 支付 | 3 |
+-------+--------+
4 rows selected (11.165 seconds)(https://www.dwsql.com)
步骤2：计算环节间转化率
Spark SQL
WITH funnel AS (
SELECT '浏览' AS step, 1 as step_order, COUNT(DISTINCT user_id) AS users FROM t11_zj_view
UNION ALL
SELECT '加购', 2 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_cart
UNION ALL
SELECT '下单', 3 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_order
UNION ALL
SELECT '支付', 4 as step_order, COUNT(DISTINCT user_id) FROM t11_zj_pay
)
SELECT
step,
step_order,
users,
lag(users) over (order by step_order) as prev_users,
round(users * 100.0 / lag(users) over (order by step_order), 1) as conversion_pct
FROM funnel
ORDER BY step_order
最终结果：
+-------+-------------+--------+-------------+-----------------+
| step | step_order | users | prev_users | conversion_pct |
+-------+-------------+--------+-------------+-----------------+
| 浏览 | 1 | 5 | NULL | NULL |
| 加购 | 2 | 4 | 5 | 80.0 |
| 下单 | 3 | 4 | 4 | 100.0 |
| 支付 | 4 | 3 | 4 | 75.0 |
+-------+-------------+--------+-------------+-----------------+
4 rows selected (1.636 seconds)(https://www.dwsql.com)
结果解读：
- 浏览→加购转化率 80%：用户5流失（浏览了A但未加购）
- 加购→下单转化率 100%：加购的4人全部下单
- 下单→支付转化率 75%：用户3流失（下单了A但未支付）
- 整体转化率（浏览→支付）：3/5 = 60%
五、常见坑点
坑1：漏斗中"整体转化率"和"环节转化率"是两回事
整体转化率 = 支付人数 / 浏览人数 = 3/5 = 60%。环节转化率是每一步的"截面"对比。面试时要区分清楚。
坑2：UNION ALL 的 step 顺序
UNION ALL 不保证顺序，LAG 依赖排序。添加step_order 来标识顺序，确保lag函数按顺序计算。
坑3：需要细粒度漏斗
上述是"用户级"漏斗（只看这个人有没有做过这个动作）。面试官可能追问：如果要看"商品级"漏斗（如"浏览了A → 加购了A → 下单了A"），需要 JOIN 商品维度来追踪同一商品的完整路径。
坑4：严格要求用户按照步骤计算转化率
样例数据相对简单，实际业务中用户可能通过其他渠道加购，在本渠道下单支付，导致中间过程缺失，不能算作转化过程，则需要更加复杂的分析。
六、举一反三
- 按天趋势：在 UNION ALL 的每个 SELECT 中加入
DATE(view_time)，可以看到每天的漏斗变化 - 多维对比：加
WHERE channel = '直播'和WHERE channel = '搜索'，对比不同渠道的漏斗 - 流失用户画像：找出"浏览了但没加购"的用户，关联用户属性表分析他们的特征（新用户？低活跃？）
七、知识点总结
| 考点 | 说明 |
|---|---|
| COUNT DISTINCT | 每个环节的去重用户数 |
| UNION ALL | 合并多环节统计结果 |
| LAG 窗口函数 | 获取上一环节人数，计算环比转化率 |
| 转化率公式 | 本环节人数 / 上一环节人数 × 100% |
八、建表语句和数据插入
点击展开 DDL & DML
-- ==========================================
-- 建表语句
-- ==========================================
CREATE TABLE IF NOT EXISTS t11_zj_view (
user_id INT,
product_id STRING,
view_time STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_cart (
user_id INT,
product_id STRING,
cart_time STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_order (
user_id INT,
product_id STRING,
order_time STRING
);
CREATE TABLE IF NOT EXISTS t11_zj_pay (
user_id INT,
product_id STRING,
pay_time STRING
);
-- 浏览数据（用户1-5，8条记录）
INSERT INTO t11_zj_view VALUES
(1, 'A', '2024-01-10 08:00:00'),
(1, 'B', '2024-01-10 08:05:00'),
(2, 'A', '2024-01-10 09:00:00'),
(2, 'C', '2024-01-10 09:30:00'),
(3, 'A', '2024-01-10 10:00:00'),
(3, 'B', '2024-01-10 10:30:00'),
(4, 'A', '2024-01-10 14:00:00'),
(5, 'A', '2024-01-10 16:00:00');
-- 加购数据（用户1-4，5条记录，用户5流失）
INSERT INTO t11_zj_cart VALUES
(1, 'A', '2024-01-10 08:10:00'),
(2, 'A', '2024-01-10 09:35:00'),
(3, 'A', '2024-01-10 10:35:00'),
(3, 'B', '2024-01-10 10:36:00'),
(4, 'A', '2024-01-10 14:30:00');
-- 下单数据（用户1-4，4条记录）
INSERT INTO t11_zj_order VALUES
(1, 'A', '2024-01-10 08:20:00'),
(2, 'A', '2024-01-10 09:40:00'),
(3, 'A', '2024-01-10 10:40:00'),
(4, 'A', '2024-01-10 14:50:00');
-- 支付数据（用户1,2,4，3条记录，用户3流失于支付环节）
INSERT INTO t11_zj_pay VALUES
(1, 'A', '2024-01-10 08:25:00'),
(2, 'A', '2024-01-10 09:45:00'),
(4, 'A', '2024-01-10 15:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
