SQL 优惠券转化漏斗：领取→使用→下单全链路转化率（阿里巴巴面试题）
一、题目背景
这道题来自阿里巴巴淘宝/天猫的营销数据分析岗面试。优惠券是电商最核心的营销工具——平台每年发放数十亿张优惠券，从"满200减30"的满减券到"限时8折"的折扣券，种类繁多。数据分析师需要通过转化漏斗衡量每种券的效果，从而优化发券策略。
业务场景：营销运营每个月经手的优惠券预算上千万，需要盯紧两个核心指标——"领了券有多少人真用了"和"用了券是不是真的促成了下单"。如果某种券的领取→使用转化率持续走低，可能说明券面额不够吸引力或使用门槛太高。
二、题目
现有三张表：领券表、用券表、下单表。计算每种券的 领取→使用转化率 和 使用→下单转化率（按券类型统计）。
领券表 t11_coupon_get：
+----------+------------+--------------+----------------------+
| user_id | coupon_id | coupon_type | get_time |
+----------+------------+--------------+----------------------+
| u01 | C001 | 满减券 | 2023-03-01 08:00:00 |
| u02 | C001 | 满减券 | 2023-03-01 09:00:00 |
| u03 | C001 | 满减券 | 2023-03-01 10:00:00 |
| u04 | C001 | 满减券 | 2023-03-01 11:00:00 |
| u05 | C001 | 满减券 | 2023-03-01 12:00:00 |
| u01 | C002 | 折扣券 | 2023-03-01 08:00:00 |
| u02 | C002 | 折扣券 | 2023-03-01 09:00:00 |
| u03 | C002 | 折扣券 | 2023-03-01 10:00:00 |
| u04 | C002 | 折扣券 | 2023-03-01 11:00:00 |
+----------+------------+--------------+----------------------+
用券表 t11_coupon_use：
+----------+------------+----------------------+
| user_id | coupon_id | use_time |
+----------+------------+----------------------+
| u01 | C001 | 2023-03-01 08:30:00 |
| u02 | C001 | 2023-03-01 09:30:00 |
| u03 | C001 | 2023-03-01 10:30:00 |
| u01 | C002 | 2023-03-01 09:00:00 |
| u02 | C002 | 2023-03-01 10:00:00 |
+----------+------------+----------------------+
下单表 t11_order
+----------+------------+-----------+----------------------+
| user_id | coupon_id | order_id | order_time |
+----------+------------+-----------+----------------------+
| u01 | C001 | O001 | 2023-03-01 08:35:00 |
| u02 | C001 | O002 | 2023-03-01 09:35:00 |
| u01 | C002 | O003 | 2023-03-01 09:30:00 |
+----------+------------+-----------+----------------------+
注意：用券表和下单表中没有 coupon_type 字段。需要想办法把券类型关联过来——这是本题的第一个考点。
三、思路分析
这是一个经典的三步转化漏斗，核心难点在于用券表和下单表缺少 coupon_type 字段，无法直接 GROUP BY。
解题分三步走：
- 领取统计：直接从领券表按
coupon_type聚合，得到每种券的领取人数（分母） - 使用统计：用券表 JOIN 领券表，拿到
coupon_type后再聚合——得到每种券的实际使用人数（分子也是下一步的分母） - 下单统计：下单表 JOIN 领券表，拿到
coupon_type后按order_id去重统计订单数 - 关联汇总：将以上三个子查询通过 LEFT JOIN 串联（保证"有领取无使用"的券不丢失），计算两步转化率
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：统计每种券的领取人数
直接从领券表按 coupon_type 聚合，这是漏斗的最上层：
select coupon_type, count(1) as get_cnt
from t11_coupon_get
group by coupon_type;
执行结果:
+--------------+----------+
| coupon_type | get_cnt |
+--------------+----------+
| 满减券 | 5 |
| 折扣券 | 4 |
+--------------+----------+
2 rows selected (8.819 seconds)(https://www.dwsql.com)
步骤2：统计每种券的使用人数
用券表没有 coupon_type，需要 JOIN 领券表来获取。注意：JOIN 条件用 user_id + coupon_id 组合键：
select g.coupon_type, count(1) as use_cnt
from t11_coupon_use u
join t11_coupon_get g
on u.user_id = g.user_id
and u.coupon_id = g.coupon_id
group by g.coupon_type;
执行结果:
+--------------+----------+
| coupon_type | use_cnt |
+--------------+----------+
| 满减券 | 3 |
| 折扣券 | 2 |
+--------------+----------+
2 rows selected (0.772 seconds)(https://www.dwsql.com)
为什么用 INNER JOIN？ 这里只统计"已使用"的券，JOIN 后自然过滤掉没被用过的领取记录。
步骤3：统计每种券的下单数
同样的问题——下单表也没有 coupon_type。JOIN 领券表获取券类型，同时对 order_id 做去重（同一张券可能对应多笔订单）：
select g.coupon_type, count(distinct o.order_id) as order_cnt
from t11_order o
join t11_coupon_get g
on o.user_id = g.user_id
and o.coupon_id = g.coupon_id
group by g.coupon_type;
执行结果:
+--------------+------------+
| coupon_type | order_cnt |
+--------------+------------+
| 折扣券 | 1 |
| 满减券 | 2 |
+--------------+------------+
2 rows selected (0.78 seconds)(https://www.dwsql.com)
步骤4：LEFT JOIN 串联 + 计算转化率
将三个子查询用 LEFT JOIN 串起来，计算两步转化率：
select t1.coupon_type,
t1.get_cnt,
coalesce(t2.use_cnt, 0) as use_cnt,
coalesce(t3.order_cnt, 0) as order_cnt,
round(coalesce(t2.use_cnt, 0) / t1.get_cnt, 4)
as get_to_use_rate,
round(coalesce(t3.order_cnt, 0)
/ nullif(coalesce(t2.use_cnt, 0), 0), 4)
as use_to_order_rate
from (
-- 领取人数
select coupon_type, count(1) as get_cnt
from t11_coupon_get
group by coupon_type
) t1
left join (
-- 使用人数（JOIN 领券表获取 coupon_type）
select g.coupon_type, count(1) as use_cnt
from t11_coupon_use u
join t11_coupon_get g
on u.user_id = g.user_id
and u.coupon_id = g.coupon_id
group by g.coupon_type
) t2 on t1.coupon_type = t2.coupon_type
left join (
-- 下单数（JOIN 领券表获取 coupon_type，订单去重）
select g.coupon_type, count(distinct o.order_id) as order_cnt
from t11_order o
join t11_coupon_get g
on o.user_id = g.user_id
and o.coupon_id = g.coupon_id
group by g.coupon_type
) t3 on t1.coupon_type = t3.coupon_type;
执行结果（与题目样例数据一一对应）：
+--------------+----------+----------+------------+------------------+--------------------+
| coupon_type | get_cnt | use_cnt | order_cnt | get_to_use_rate | use_to_order_rate |
+--------------+----------+----------+------------+------------------+--------------------+
| 满减券 | 5 | 3 | 2 | 0.6 | 0.6667 |
| 折扣券 | 4 | 2 | 1 | 0.5 | 0.5 |
+--------------+----------+----------+------------+------------------+--------------------+
2 rows selected (1.412 seconds)(https://www.dwsql.com)
数据解读：满减券领取→使用转化率 60%，高于折扣券的 50%，说明满减券对用户更有吸引力。但在使用→下单环节，满减券的 66.67% 显著高于折扣券的 50%，说明使用满减券后下单意愿更强，"凑满减"的心理门槛反而促进了成交。
五、常见坑点
坑1：分母为零时转化率计算崩溃
如果某种券领取数为 0 或使用数为 0，直接除法会报错。使用 coalesce(..., 0) 将 NULL 转为 0，并在第二个转化率的分母上使用 nullif(x, 0) —— 当分母为 0 时返回 NULL 而非报错。
-- 错误写法
round(t2.use_cnt / t1.get_cnt, 4)
-- 正确写法：防止分母为 0
round(coalesce(t2.use_cnt, 0) / nullif(t1.get_cnt, 0), 4)
坑2：用 INNER JOIN 串联会丢失"有领取但无人使用"的券
如果领券表的某种券在子查询 t2 中没有匹配记录，INNER JOIN 会导致整行消失。"领取→使用转化率 = 0" 本身是一个重要业务信号——表明该券种彻底失效。始终坚持从漏斗最上层用 LEFT JOIN。
坑3：用券表/下单表没有 coupon_type，不能直接 GROUP BY
这是本题最容易忽略的细节。t11_coupon_use 和 t11_order 只有 user_id 和 coupon_id，必须 JOIN t11_coupon_get 才能拿到 coupon_type。如果面试中没注意到这点就直接写 group by coupon_type，会被面试官指出表结构问题。
坑4：下单表要用 COUNT(DISTINCT order_id)
同一张券可能在不同时间被同一用户用于多笔订单。直接用 COUNT(1) 会重复计数，COUNT(DISTINCT order_id) 才是真实订单数。
六、举一反三
- 加入时间维度：WHERE 限定 get_time / use_time / order_time 的日期范围，计算"最近 7 天每种券的转化率趋势"
- 按用户分层：GROUP BY 加上 user_level（新用户 / 老用户 / 会员），对比不同用户群体对券的敏感度
- 券面额维度：如果券表有
discount_amount字段，按面额范围（5 / 10 / 20 / 50 元）统计转化率，找到最佳优惠力度 - A/B 实验对比：加 experiment_id 分组，评估不同券策略的实际效果
- 全链路漏斗：把三个子查询合并成一个宽表，用
CASE WHEN打标每个用户处于漏斗的哪个阶段，做桑基图分析
七、知识点总结
| 考点 | 说明 |
|---|---|
| LEFT JOIN 漏斗串联 | 从顶层（领取）向左关联下层（使用、下单），保留所有入口数据 |
| 跨表获取字段 | 用券表/下单表无 coupon_type，必须 JOIN 领券表补充 |
| 子查询 + GROUP BY | 每个阶段独立聚合后再 JOIN，逻辑清晰可维护 |
| COALESCE + NULLIF | COALESCE 将 NULL 转为 0，NULLIF 防止除以 0 |
| COUNT DISTINCT | 下单表中按 order_id 去重统计真实订单数 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 领券表
CREATE TABLE t11_coupon_get (
user_id string COMMENT '用户ID',
coupon_id string COMMENT '优惠券ID',
coupon_type string COMMENT '券类型',
get_time string COMMENT '领取时间'
) COMMENT '用户领券记录表';
-- 用券表
CREATE TABLE t11_coupon_use (
user_id string COMMENT '用户ID',
coupon_id string COMMENT '优惠券ID',
use_time string COMMENT '使用时间'
) COMMENT '用户用券记录表';
-- 下单表
CREATE TABLE t11_order (
user_id string COMMENT '用户ID',
coupon_id string COMMENT '使用的优惠券ID',
order_id string COMMENT '订单ID',
order_time string COMMENT '下单时间'
) COMMENT '用户下单记录表';
-- 领券数据（9条）
INSERT INTO t11_coupon_get VALUES
('u01','C001','满减券','2023-03-01 08:00:00'),
('u02','C001','满减券','2023-03-01 09:00:00'),
('u03','C001','满减券','2023-03-01 10:00:00'),
('u04','C001','满减券','2023-03-01 11:00:00'),
('u05','C001','满减券','2023-03-01 12:00:00'),
('u01','C002','折扣券','2023-03-01 08:00:00'),
('u02','C002','折扣券','2023-03-01 09:00:00'),
('u03','C002','折扣券','2023-03-01 10:00:00'),
('u04','C002','折扣券','2023-03-01 11:00:00');
-- 用券数据（5条）
INSERT INTO t11_coupon_use VALUES
('u01','C001','2023-03-01 08:30:00'),
('u02','C001','2023-03-01 09:30:00'),
('u03','C001','2023-03-01 10:30:00'),
('u01','C002','2023-03-01 09:00:00'),
('u02','C002','2023-03-01 10:00:00');
-- 下单数据（3条）
INSERT INTO t11_order VALUES
('u01','C001','O001','2023-03-01 08:35:00'),
('u02','C001','O002','2023-03-01 09:35:00'),
('u01','C002','O003','2023-03-01 09:30:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
