SQL KOL带货转化漏斗：曝光→点击→加购→购买（小红书面试题）
一、题目
统计每篇 KOL 带货笔记的转化漏斗：曝光 UV → 点击 UV → 加购 UV → 下单 UV，以及相邻环节的转化率（点击率、加购率、下单率）。
假设有四张表：
t6_note_exposure：笔记曝光记录t6_note_click：商品链接点击记录t6_cart_add：加购记录t6_order_create：下单记录
-- t6_note_exposure 曝光表
+----------+----------+-------------------+
| note_id | user_id | exposure_time |
+----------+----------+-------------------+
| N001 | u01 | 2025-06-01 10:00 |
| N001 | u02 | 2025-06-01 10:05 |
| N001 | u03 | 2025-06-01 10:10 |
| N002 | u01 | 2025-06-01 11:00 |
| N002 | u04 | 2025-06-01 11:05 |
+----------+----------+-------------------+
-- t6_note_click 点击表
+----------+----------+-------------------+
| note_id | user_id | click_time |
+----------+----------+-------------------+
| N001 | u01 | 2025-06-01 10:02 |
| N001 | u03 | 2025-06-01 10:12 |
| N002 | u01 | 2025-06-01 11:02 |
+----------+----------+-------------------+
-- t6_cart_add 加购表
+----------+----------+-------------------+
| note_id | user_id | cart_add_time |
+----------+----------+-------------------+
| N001 | u01 | 2025-06-01 10:05 |
| N001 | u03 | 2025-06-01 10:15 |
+----------+----------+-------------------+
-- t6_order_create 下单表
+-----------+----------+----------+-------------------+
| order_id | note_id | user_id | order_time |
+-----------+----------+----------+-------------------+
| 1001 | N001 | u01 | 2025-06-01 10:10 |
+-----------+----------+----------+-------------------+
二、思路分析
- 经典漏斗分析，以曝光表为基准，LEFT JOIN 各环节表；
- 按 note_id 分组，使用 COUNT DISTINCT 统计各环节UV；
- 计算相邻环节转化率：点击率 = 点击UV/曝光UV，加购率 = 加购UV/点击UV，下单率 = 下单UV/加购UV。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、逐步推导
1.统计每个笔记各环节UV
执行SQL
select e.note_id,
count(distinct e.user_id) as exposure_uv,
count(distinct c.user_id) as click_uv,
count(distinct a.user_id) as cart_add_uv,
count(distinct o.user_id) as order_uv
from t6_note_exposure e
left join t6_note_click c
on e.note_id = c.note_id and e.user_id = c.user_id
left join t6_cart_add a
on e.note_id = a.note_id and e.user_id = a.user_id
left join t6_order_create o
on e.note_id = o.note_id and e.user_id = o.user_id
group by e.note_id
查询结果
+----------+--------------+-----------+--------------+-----------+
| note_id | exposure_uv | click_uv | cart_add_uv | order_uv |
+----------+--------------+-----------+--------------+-----------+
| N001 | 3 | 2 | 2 | 1 |
| N002 | 2 | 1 | 0 | 0 |
+----------+--------------+-----------+--------------+-----------+
2 rows selected (2.347 seconds)(https://www.dwsql.com)
2.计算各环节转化率
执行SQL
select note_id,
exposure_uv,
click_uv,
round(click_uv / nullif(exposure_uv, 0), 4) as exposure_to_click_rate,
cart_add_uv,
round(cart_add_uv / nullif(click_uv, 0), 4) as click_to_cart_rate,
order_uv,
round(order_uv / nullif(cart_add_uv, 0), 4) as cart_to_order_rate
from (
select e.note_id,
count(distinct e.user_id) as exposure_uv,
count(distinct c.user_id) as click_uv,
count(distinct a.user_id) as cart_add_uv,
count(distinct o.user_id) as order_uv
from t6_note_exposure e
left join t6_note_click c
on e.note_id = c.note_id and e.user_id = c.user_id
left join t6_cart_add a
on e.note_id = a.note_id and e.user_id = a.user_id
left join t6_order_create o
on e.note_id = o.note_id and e.user_id = o.user_id
group by e.note_id
) t
查询结果
+----------+--------------+-----------+-------------------------+--------------+---------------------+-----------+---------------------+
| note_id | exposure_uv | click_uv | exposure_to_click_rate | cart_add_uv | click_to_cart_rate | order_uv | cart_to_order_rate |
+----------+--------------+-----------+-------------------------+--------------+---------------------+-----------+---------------------+
| N001 | 3 | 2 | 0.6667 | 2 | 1.0 | 1 | 0.5 |
| N002 | 2 | 1 | 0.5 | 0 | 0.0 | 0 | NULL |
+----------+--------------+-----------+-------------------------+--------------+---------------------+-----------+---------------------+
2 rows selected (1.144 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：四表LEFT JOIN导致行膨胀 — 以曝光表为基准同时LEFT JOIN点击、加购、下单三张表，形成1曝光 x N点击 x M加购 x K下单的笛卡尔积膨胀。例如N001的u01同时在四张表中有记录，JOIN后该行在中间结果集中被展开多次，直接count会放大聚合值，必须使用 count(distinct) 逐表去重才能得到正确的UV。
坑2：分母为0时转化率异常 — 漏斗下游环节UV可能为0（如N002的加购UV=0），直接除法 order_uv / cart_add_uv 即 0/0 在某些SQL引擎中会报错或返回NaN，使用 nullif(分母, 0) 将0转为NULL，除法结果安全返回NULL。业务上NULL表示该环节无数据、转化率不可计算。
坑3：LEFT JOIN的ON条件需双重匹配 — ON条件必须同时包含 note_id 和 user_id，仅匹配 note_id 会导致用户维度数据串扰。例如N001的u01曝光行会错误匹配到N001的u03点击行，而实际上u03的点击与u01的曝光并非同一用户路径。
五、知识点总结
| 考点 | 说明 |
|---|---|
| 多表LEFT JOIN双重匹配 | 以曝光表为基准，ON条件同时包含note_id+user_id，确保用户维度的行为路径一致，防止跨用户数据串扰 |
| COUNT DISTINCT去重 | 多表JOIN产生行膨胀后，count(distinct)按原始表维度去重，是漏斗UV统计的标准写法 |
| nullif防除零 | round(value / nullif(denominator, 0), 4) 安全处理分母为0的场景，返回NULL而非报错 |
| 漏斗转化分析模式 | GROUP BY分组 + COUNT DISTINCT聚合 + 环比转化率计算，是电商/内容平台漏斗分析的通用SQL模板 |
六、建表语句
点击展开 DDL & DML
-- 建表语句
create table t6_note_exposure (
note_id string comment '笔记id',
user_id string comment '用户id',
exposure_time string comment '曝光时间'
) comment '笔记曝光记录表';
create table t6_note_click (
note_id string comment '笔记id',
user_id string comment '用户id',
click_time string comment '点击时间'
) comment '商品链接点击记录表';
create table t6_cart_add (
note_id string comment '笔记id',
user_id string comment '用户id',
cart_add_time string comment '加购时间'
) comment '加购记录表';
create table t6_order_create (
order_id bigint comment '订单id',
note_id string comment '笔记id',
user_id string comment '用户id',
order_time string comment '下单时间'
) comment '下单记录表';
-- 插入数据
insert into t6_note_exposure(note_id, user_id, exposure_time) values
('N001','u01','2025-06-01 10:00'),
('N001','u02','2025-06-01 10:05'),
('N001','u03','2025-06-01 10:10'),
('N002','u01','2025-06-01 11:00'),
('N002','u04','2025-06-01 11:05');
insert into t6_note_click(note_id, user_id, click_time) values
('N001','u01','2025-06-01 10:02'),
('N001','u03','2025-06-01 10:12'),
('N002','u01','2025-06-01 11:02');
insert into t6_cart_add(note_id, user_id, cart_add_time) values
('N001','u01','2025-06-01 10:05'),
('N001','u03','2025-06-01 10:15');
insert into t6_order_create(order_id, note_id, user_id, order_time) values
(1001,'N001','u01','2025-06-01 10:10');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
