美团大数据面试SQL-优惠券核销率分析
⚠️ 待修正
一、题目背景
这道题来自美团的数据分析岗面试。美团是本地生活服务（外卖、到店、酒旅），数据分析师需要从海量业务数据中挖掘洞见。
业务场景：数据分析相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有两张表：优惠券领取表 t7_coupon_receive 和优惠券使用表 t7_coupon_use。请统计每种优惠券的核销率（使用数/领取数）。
优惠券领取表 t7_coupon_receive：
+----------+----------+-------------+---------------------+
| coupon_id| user_id | coupon_type | receive_time |
+----------+----------+-------------+---------------------+
| CP001 | u01 | 满减券 | 2023-03-01 10:00:00 |
| CP002 | u01 | 折扣券 | 2023-03-01 10:30:00 |
| CP001 | u02 | 满减券 | 2023-03-01 11:00:00 |
| CP003 | u02 | 免配送费券 | 2023-03-01 12:00:00 |
| CP001 | u03 | 满减券 | 2023-03-02 09:00:00 |
| CP002 | u03 | 折扣券 | 2023-03-02 10:00:00 |
| CP002 | u04 | 折扣券 | 2023-03-02 10:30:00 |
| CP003 | u04 | 免配送费券 | 2023-03-02 11:00:00 |
+----------+----------+-------------+---------------------+
优惠券使用表 t7_coupon_use：
+----------+----------+---------------------+
| coupon_id| user_id | use_time |
+----------+----------+---------------------+
| CP001 | u01 | 2023-03-01 11:00:00 |
| CP001 | u02 | 2023-03-01 12:30:00 |
| CP002 | u01 | 2023-03-01 14:00:00 |
| CP002 | u03 | 2023-03-02 11:00:00 |
| CP003 | u04 | 2023-03-02 12:00:00 |
+----------+----------+---------------------+
三、思路分析
核销率是衡量优惠券发放效果的核心指标，属于转化漏斗分析。
解题步骤：
- 从领取表按
coupon_type统计领券次数； - 从使用表统计用券次数；
- 通过 LEFT JOIN 关联，计算核销率 = 用券数 / 领券数；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 统计各类券的核销率
执行SQL
select t1.coupon_type,
t1.receive_cnt,
coalesce(t2.use_cnt, 0) as use_cnt,
round(coalesce(t2.use_cnt, 0) / t1.receive_cnt, 4) as redemption_rate
from (
select coupon_type,
count(1) as receive_cnt
from t7_coupon_receive
group by coupon_type
) t1
left join (
select t3.coupon_type,
count(1) as use_cnt
from t7_coupon_use t2
join t7_coupon_receive t3
on t2.coupon_id = t3.coupon_id
and t2.user_id = t3.user_id
group by t3.coupon_type
) t2
on t1.coupon_type = t2.coupon_type
order by redemption_rate desc
执行结果
+-------------+-------------+---------+------------------+
| coupon_type | receive_cnt | use_cnt | redemption_rate |
+-------------+-------------+---------+------------------+
| 满减券 | 3 | 2 | 0.6667 |
| 折扣券 | 3 | 2 | 0.6667 |
| 免配送费券 | 2 | 1 | 0.5000 |
+-------------+-------------+---------+------------------+
满减券和折扣券核销率均为66.67%，免配送费券核销率仅50%，可能需要调整券面额或推送策略。
五、常见坑点
坑1：各步骤COUNT DISTINCT口径需一致 — 如果时间窗口不同，转化率可能 >100%，出现诡异数据。
坑2：LEFT JOIN链过长导致性能爆炸 — 3-4层嵌套在大数据量下严重膨胀，可用 UNION ALL + CASE WHEN 替代。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| COUNT DISTINCT 去重 | 统计唯一用户/事件数，避免重复记录干扰聚合结果 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句：优惠券领取表
CREATE TABLE t7_coupon_receive (
coupon_id string COMMENT '优惠券ID',
user_id string COMMENT '用户ID',
coupon_type string COMMENT '券类型',
receive_time string COMMENT '领取时间'
) COMMENT '优惠券领取表';
-- 建表语句：优惠券使用表
CREATE TABLE t7_coupon_use (
coupon_id string COMMENT '优惠券ID',
user_id string COMMENT '用户ID',
use_time string COMMENT '使用时间'
) COMMENT '优惠券使用表';
-- 数据插入
INSERT INTO t7_coupon_receive VALUES
('CP001', 'u01', '满减券', '2023-03-01 10:00:00'),
('CP002', 'u01', '折扣券', '2023-03-01 10:30:00'),
('CP001', 'u02', '满减券', '2023-03-01 11:00:00'),
('CP003', 'u02', '免配送费券', '2023-03-01 12:00:00'),
('CP001', 'u03', '满减券', '2023-03-02 09:00:00'),
('CP002', 'u03', '折扣券', '2023-03-02 10:00:00'),
('CP002', 'u04', '折扣券', '2023-03-02 10:30:00'),
('CP003', 'u04', '免配送费券', '2023-03-02 11:00:00');
INSERT INTO t7_coupon_use VALUES
('CP001', 'u01', '2023-03-01 11:00:00'),
('CP001', 'u02', '2023-03-01 12:30:00'),
('CP002', 'u01', '2023-03-01 14:00:00'),
('CP002', 'u03', '2023-03-02 11:00:00'),
('CP003', 'u04', '2023-03-02 12:00:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
