阿里巴巴大数据面试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 |
| u01      | C002       | 折扣券      | 2023-03-01 08:00:00 |
| u02      | C002       | 折扣券      | 2023-03-01 09:00:00 |
+----------+------------+-------------+---------------------+

用券表 t11_coupon_use： user_id, coupon_id, use_time

下单表 t11_order： user_id, coupon_id, order_id, order_time

三、思路分析

这是一个经典的三步转化漏斗，核心是用 LEFT JOIN 串联三个阶段的统计数据：

1. 领取阶段：按 coupon_type 统计领取人数（分母）
2. 使用阶段：LEFT JOIN 领取数据，统计使用了的人数
3. 下单阶段：LEFT JOIN 使用数据，统计最终下单的订单数
4. 计算转化率：使用数/领取数、下单数/使用数，注意用 COALESCE 处理 NULL

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

四、逐步推导

步骤1：按券类型分别统计各阶段的计数

每个券类型独立子查询统计领取数、使用数、下单数：

-- 领取数
select coupon_type, count(1) as get_cnt from t11_coupon_get group by coupon_type

-- 使用数
select coupon_type, count(1) as use_cnt from t11_coupon_use group by coupon_type

-- 下单数
select coupon_type, count(distinct order_id) as order_cnt from t11_order group by coupon_type

步骤2：LEFT JOIN 串联 + 转化率计算

执行SQL

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)/coalesce(t2.use_cnt,1), 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 (select coupon_type, count(1) as use_cnt from t11_coupon_use group by coupon_type) t2
    on t1.coupon_type = t2.coupon_type
left join (select coupon_type, count(distinct order_id) as order_cnt
           from t11_order group by 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|
+-------------+---------+---------+-----------+----------------+------------------+
| 满减券      | 100     | 60      | 45        | 0.6000         | 0.7500           |
| 折扣券      | 80      | 40      | 30        | 0.5000         | 0.7500           |
+-------------+---------+---------+-----------+----------------+------------------+

满减券领取→使用转化率 60% 显著高于折扣券的 50%，建议多发满减券。两种券的使用→下单转化率都是 75%，说明只要用了券，下单意愿是一致的。

五、常见坑点

坑1：分母为0时转化率计算崩溃

如果某种券没有人领取（get_cnt=0）或没有人使用（use_cnt=0），除法会报错或返回 NULL。使用 coalesce(t2.use_cnt, 0) 并加 NULLIF 或外层 WHERE 过滤空分母。

坑2：用 INNER JOIN 会丢失"领取但无人使用"的券

如果用 INNER JOIN 串联三个阶段，第一个没有"使用记录"的券类型会直接消失。"领取→使用=0"本身是一个重要数据洞察，不能丢。始终坚持从最上层的漏斗用 LEFT JOIN。

坑3：下单表中的 COUNT 要用 DISTINCT order_id

同一张券可能对应多笔订单（如果一个订单包含多种商品），直接用 COUNT(1) 会把订单数算多。COUNT(DISTINCT order_id) 才是真实订单数。

六、举一反三

1. 加入时间维度：WHERE 限定 get_time/use_time/order_time 的日期范围，计算"最近7天每种券的转化率趋势"
2. 按用户分层：GROUP BY 加上 user_level（新用户/老用户/会员），对比不同用户群体对券的敏感度
3. 券面额维度：如果券表有 discount_amount（优惠金额）字段，按面额范围（5/10/20/50元）统计转化率，找到最佳优惠力度
4. A/B实验对比：加 experiment_id 分组，评估不同券策略（如"满200-30 vs 满150-25"）的实际效果

七、知识点总结

考点	说明
LEFT JOIN 漏斗串联	从顶层（领取）向左关联下层（使用、下单），保留所有入口数据
COUNT DISTINCT	下单表中按 order_id 去重统计真实订单数
COALESCE 处理 NULL	无使用/下单记录的券类型，将 NULL 转为 0
子查询 + GROUP BY	每个阶段独立聚合后再 JOIN，逻辑清晰可维护

八、建表语句和数据插入

点击展开 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 '用户下单记录表';

-- 样例数据（已简化，实际数据量更大）
INSERT INTO t11_coupon_get VALUES
('u01','C001','满减券','2023-03-01 08:00:00'),
('u02','C001','满减券','2023-03-01 09:00:00'),
('u01','C002','折扣券','2023-03-01 08:00:00');

INSERT INTO t11_coupon_use VALUES
('u01','C001','2023-03-01 08:30:00'),
('u01','C002','2023-03-01 09:30:00');

INSERT INTO t11_order VALUES
('u01','C001','O001','2023-03-01 08:35:00');

📱关注公众号

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

💬加群交流

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