拼多多大数据面试SQL-退款率TOP 10的商品和品类
⚠️ 待修正
一、题目背景
这道题来自拼多多的数据分析岗面试。拼多多是社交裂变电商，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
计算每个商品的退款率（退款率 = 退款订单数 / 总订单数），并按退款率降序输出Top10商品及其所属品类。
假设有两张表：
product_orders：商品订单表（含下单记录）refund_orders：退款订单表
-- product_orders 商品订单表
+--------+----------+------------+----------+
| prod_id| order_id | order_time | category |
+--------+----------+------------+----------+
| P001 | 1001 | 2025-06-01 | 服装 |
| P002 | 1002 | 2025-06-01 | 服装 |
| P001 | 1003 | 2025-06-02 | 服装 |
| P003 | 1004 | 2025-06-02 | 食品 |
| P001 | 1005 | 2025-06-03 | 服装 |
| P003 | 1006 | 2025-06-03 | 食品 |
| P002 | 1007 | 2025-06-04 | 服装 |
+--------+----------+------------+----------+
-- refund_orders 退款订单表
+----------+--------+-------------------+
| order_id | prod_id| refund_time |
+----------+--------+-------------------+
| 1001 | P001 | 2025-06-03 10:00 |
| 1003 | P001 | 2025-06-05 11:00 |
| 1005 | P001 | 2025-06-06 08:00 |
| 1006 | P003 | 2025-06-06 16:00 |
+----------+--------+-------------------+
三、思路分析
- 先统计每个商品的总订单数，再统计每个商品的退款订单数；
- 使用
LEFT JOIN关联两张表，避免退款为0的商品被遗漏； - 退款率 = 退款订单数 / 总订单数，按退款率降序取Top10。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1.分别统计每个商品的总订单数和退款订单数
执行SQL
select p.prod_id,
p.category,
count(distinct p.order_id) as total_orders,
count(distinct r.order_id) as refund_orders
from product_orders p
left join refund_orders r
on p.prod_id = r.prod_id and p.order_id = r.order_id
group by p.prod_id, p.category
查询结果
+--------+----------+--------------+---------------+
| prod_id| category | total_orders | refund_orders |
+--------+----------+--------------+---------------+
| P001 | 服装 | 3 | 3 |
| P002 | 服装 | 2 | 0 |
| P003 | 食品 | 2 | 1 |
+--------+----------+--------------+---------------+
2.计算退款率并取Top10
执行SQL
select prod_id,
category,
total_orders,
refund_orders,
round(refund_orders / total_orders, 4) as refund_rate
from (
select p.prod_id,
p.category,
count(distinct p.order_id) as total_orders,
count(distinct r.order_id) as refund_orders
from product_orders p
left join refund_orders r
on p.prod_id = r.prod_id and p.order_id = r.order_id
group by p.prod_id, p.category
) t
order by refund_rate desc
limit 10
查询结果
+--------+----------+--------------+---------------+---------------+-------------+
| prod_id| category | total_orders | refund_orders | refund_rate |
+--------+----------+--------------+---------------+---------------+
| P001 | 服装 | 3 | 3 | 1.0000 |
| P003 | 食品 | 2 | 1 | 0.5000 |
| P002 | 服装 | 2 | 0 | 0.0000 |
+--------+----------+--------------+---------------+---------------+-------------+
五、常见坑点
坑1：RANK vs DENSE_RANK vs ROW_NUMBER — 并列值处理不同：ROW_NUMBER硬区分，RANK跳跃，DENSE_RANK连续。统计"前N名"需明确业务倾向。
坑2：NULL值排序位置 — ORDER BY 默认 NULLS LAST，如果排序列有 NULL，Top N可能被挤掉。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE product_orders (
prod_id string COMMENT '商品ID',
order_id bigint COMMENT '订单ID',
order_time string COMMENT '下单时间',
category string COMMENT '商品品类'
) COMMENT '商品订单表';
CREATE TABLE refund_orders (
order_id bigint COMMENT '订单ID',
prod_id string COMMENT '商品ID',
refund_time string COMMENT '退款时间'
) COMMENT '退款订单表';
-- 插入数据
insert into product_orders(prod_id, order_id, order_time, category) values
('P001', 1001, '2025-06-01', '服装'),
('P002', 1002, '2025-06-01', '服装'),
('P001', 1003, '2025-06-02', '服装'),
('P003', 1004, '2025-06-02', '食品'),
('P001', 1005, '2025-06-03', '服装'),
('P003', 1006, '2025-06-03', '食品'),
('P002', 1007, '2025-06-04', '服装');
insert into refund_orders(order_id, prod_id, refund_time) values
(1001, 'P001', '2025-06-03 10:00'),
(1003, 'P001', '2025-06-05 11:00'),
(1005, 'P001', '2025-06-06 08:00'),
(1006, 'P003', '2025-06-06 16:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
