拼多多大数据面试SQL-新用户首单品类偏好分析
⚠️ 待修正
一、题目背景
这道题来自拼多多的数据分析岗面试。拼多多是社交裂变电商，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：数据分析相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
分析新用户（注册后7天内首次下单）的首单品类偏好分布，统计每个品类作为首单品类的新用户数及占比。
假设有两张表：
user_register：用户注册表order_info：用户订单表
-- user_register 用户注册表
+---------+-------------------+
| user_id | register_time |
+---------+-------------------+
| u01 | 2025-06-01 08:00 |
| u02 | 2025-06-03 10:00 |
| u03 | 2025-06-05 11:00 |
| u04 | 2025-06-08 09:00 |
+---------+-------------------+
-- order_info 订单表
+----------+---------+----------+-------------------+
| order_id | user_id | category | order_time |
+----------+---------+----------+-------------------+
| 1001 | u01 | 食品 | 2025-06-02 10:00 |
| 1002 | u01 | 服装 | 2025-06-05 14:00 |
| 1003 | u02 | 数码 | 2025-06-04 09:00 |
| 1004 | u02 | 食品 | 2025-06-10 16:00 |
| 1005 | u03 | 服装 | 2025-06-08 12:00 |
| 1006 | u04 | 食品 | 2025-06-12 10:00 |
| 1007 | u04 | 数码 | 2025-06-15 08:00 |
| 1008 | u03 | 食品 | 2025-06-12 15:00 |
+----------+---------+----------+-------------------+
三、思路分析
- 先
JOIN用户注册表和订单表，筛选注册后7天内的订单（datediff(order_time, register_time) <= 7）； - 使用
ROW_NUMBER按用户分组、按下单时间排序，标记每个用户的首单（序号=1）； - 筛选首单记录，按品类分组统计用户数及占比。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1.筛选新用户首单记录
执行SQL
select user_id,
category,
order_time,
row_number() over (partition by user_id order by order_time) as rn
from (
select o.user_id,
o.category,
o.order_time
from user_register r
join order_info o
on r.user_id = o.user_id
where datediff(o.order_time, r.register_time) <= 7
) t
查询结果
+---------+----------+-------------------+-----+
| user_id | category | order_time | rn |
+---------+----------+-------------------+-----+
| u01 | 食品 | 2025-06-02 10:00 | 1 |
| u01 | 服装 | 2025-06-05 14:00 | 2 |
| u02 | 数码 | 2025-06-04 09:00 | 1 |
| u03 | 服装 | 2025-06-08 12:00 | 1 |
| u04 | 食品 | 2025-06-12 10:00 | 1 |
+---------+----------+-------------------+-----+
2.按品类统计新用户数及占比
执行SQL
select category,
count(distinct user_id) as new_user_cnt,
round(count(distinct user_id) / sum(count(distinct user_id)) over(), 4) as ratio
from (
select user_id,
category,
order_time,
row_number() over (partition by user_id order by order_time) as rn
from (
select o.user_id,
o.category,
o.order_time
from user_register r
join order_info o
on r.user_id = o.user_id
where datediff(o.order_time, r.register_time) <= 7
) t
) tt
where rn = 1
group by category
查询结果
+----------+--------------+--------+
| category | new_user_cnt | ratio |
+----------+--------------+--------+
| 食品 | 2 | 0.5000 |
| 数码 | 1 | 0.2500 |
| 服装 | 1 | 0.2500 |
+----------+--------------+--------+
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER + 差值法 | 连续问题经典解法：日期-行号=分组标识，相同差值同一连续段 |
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE user_register (
user_id string COMMENT '用户ID',
register_time string COMMENT '注册时间'
) COMMENT '用户注册表';
CREATE TABLE order_info (
order_id bigint COMMENT '订单ID',
user_id string COMMENT '用户ID',
category string COMMENT '商品品类',
order_time string COMMENT '下单时间'
) COMMENT '用户订单表';
-- 插入数据
insert into user_register(user_id, register_time) values
('u01', '2025-06-01 08:00'),
('u02', '2025-06-03 10:00'),
('u03', '2025-06-05 11:00'),
('u04', '2025-06-08 09:00');
insert into order_info(order_id, user_id, category, order_time) values
(1001, 'u01', '食品', '2025-06-02 10:00'),
(1002, 'u01', '服装', '2025-06-05 14:00'),
(1003, 'u02', '数码', '2025-06-04 09:00'),
(1004, 'u02', '食品', '2025-06-10 16:00'),
(1005, 'u03', '服装', '2025-06-08 12:00'),
(1006, 'u04', '食品', '2025-06-12 10:00'),
(1007, 'u04', '数码', '2025-06-15 08:00'),
(1008, 'u03', '食品', '2025-06-12 15:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
