阿里巴巴大数据面试SQL-优惠券使用转化率

数据仓库晨曦

发布于 2026-07-11 10:12:39

1770

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题来自阿里巴巴淘宝/天猫的营销数据分析岗面试。优惠券是电商最核心的营销工具——平台每年发放数十亿张优惠券，从"满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 去重统计真实订单数

八、建表语句和数据插入

-- 领券表
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');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-07，如有侵权请联系 cloudcommunity@tencent.com 删除

阿里巴巴