阿里巴巴大数据面试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。 解题分三步走： 1. **领取统计**：直接从领券表按 `coupon_type` 聚合，得到每种券的领取人数（分母） 1. **使用统计**：用券表 JOIN 领券表，拿到 `coupon_type` 后再聚合——得到每种券的**实际使用人数**（分子也是下一步的分母） 1. **下单统计**：下单表 JOIN 领券表，拿到 `coupon_type` 后按 `order_id` 去重统计订单数 1. **关联汇总**：将以上三个子查询通过 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)` 才是真实订单数。 ## **六、举一反三** 1. **加入时间维度**：WHERE 限定 get_time / use_time / order_time 的日期范围，计算"最近 7 天每种券的转化率趋势" 1. **按用户分层**：GROUP BY 加上 user_level（新用户 / 老用户 / 会员），对比不同用户群体对券的敏感度 1. **券面额维度**：如果券表有 `discount_amount` 字段，按面额范围（5 / 10 / 20 / 50 元）统计转化率，找到最佳优惠力度 1. **A/B 实验对比**：加 experiment_id 分组，评估不同券策略的实际效果 1. **全链路漏斗**：把三个子查询合并成一个宽表，用 `CASE WHEN` 打标每个用户处于漏斗的哪个阶段，做桑基图分析 ## **七、知识点总结** |考点|说明| | --- | --- | |LEFT JOIN 漏斗串联|从顶层（领取）向左关联下层（使用、下单），保留所有入口数据| |跨表获取字段|用券表/下单表无 coupon_type，必须 JOIN 领券表补充| |子查询 + GROUP BY|每个阶段独立聚合后再 JOIN，逻辑清晰可维护| |COALESCE + NULLIF|COALESCE 将 NULL 转为 0，NULLIF 防止除以 0| |COUNT DISTINCT|下单表中按 order_id 去重统计真实订单数| ## **八、建表语句和数据插入** ``` -- 领券表 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'); ```
