SQL 新用户首单品类偏好：首次购买品类分布（拼多多面试题）

一、题目背景

这道题来自拼多多用户增长部门的数据分析岗面试。拼多多的获客成本（CAC）在电商行业中处于低位，核心依赖微信社交裂变和下沉市场自然增长。但"低成本获客"不等于"高质量留存"——新用户的7日留存率和首单转化率是增长团队最核心的OKR指标。

一个关键策略问题是：新用户注册后的第一单，倾向于买什么品类？是延续拼多多低价心智的日用百货（纸巾、垃圾袋），还是百亿补贴引导下的大牌美妆和数码产品？ 这个问题的答案直接决定了新人专区的品类排布、首单优惠券的面额和适用品类、以及不同渠道来源（微信拼团 vs 应用商店下载 vs 信息流广告）的差异化承接策略。

数据层面，这个场景需要关联两张表——用户注册表和订单表——核心挑战在于两个过滤条件的组合：注册后7天内的订单 AND 每个用户的首单（按下单时间最早的订单）。where datediff <= 7 过滤时间窗口，row_number() = 1 定位首单。

业务场景：增长团队每月输出"新用户首单品类偏好报告"，按注册渠道拆分，用于指导新人专区的品类选品和AB实验设计。考察的是日期函数 datediff、窗口函数 row_number 取首单、以及占比计算 sum() over() 的组合应用。

二、题目

分析新用户（注册后7天内首次下单）的首单品类偏好分布，统计每个品类作为首单品类的新用户数及占比。

假设有两张表：

• t9_user_register：用户注册表
• t9_order_info：用户订单表

-- t9_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  |
+---------+-------------------+

-- t9_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  |
+----------+---------+----------+-------------------+

三、思路分析

1. 先 join 用户注册表和订单表，筛选注册后7天内的订单（datediff(order_time, register_time) <= 7）；
2. 使用 row_number() over (partition by user_id order by order_time) 按用户分组、按下单时间排序，标记每个用户的首单（序号=1）；
3. 筛选首单记录，按品类分组统计用户数及占比。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

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 t9_user_register r
    join t9_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 t9_user_register r
        join t9_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：datediff 在不同 SQL 引擎中的行为差异 — Spark SQL 中 datediff(end, start) 返回日期差（天数），而 Hive 中行为相同但参数顺序易混淆。务必确认引擎文档，防止筛选窗口计算错误，导致本该算作首单的订单被漏掉。

坑2：注册后7天内无订单的用户会被静默排除 — 内连接 join 只保留有订单的用户。如果需求是统计"所有新用户的首单品类分布"且要求无订单用户也展示（品类为"无"），需改用 left join 并用 coalesce 填充默认品类。

坑3：同一用户在同一时间有多个订单时首单不确定 — row_number() 对 order by order_time 相同的行会随机分配序号，导致同一用户在不同查询中可能返回不同的"首单"品类。如需确定性结果，应在 order by 中增加第二排序字段（如 order_id）。

六、举一反三

1. 对比新老用户的品类偏好差异：在订单表中将用户分为新用户（注册7天内）和老用户，按品类分别统计购买人数占比，观察新老用户在品类选择上的结构性差异，为新人专区和老客推荐提供选品依据。

2. 30天内二次购买品类分析：筛选首单在注册7天内的用户，进一步分析他们在30天内是否产生了二单，以及二单品类与首单品类的重合度，评估首单品类对复购粘性的引导效果。

3. 按注册渠道拆分首单品类偏好：在注册表中增加 channel 字段（如"微信拼团"、"APP直接下载"、"小程序"），按渠道分组统计首单品类分布，判断不同渠道来的新用户是否存在品类偏好差异，指导渠道精细化运营。

七、知识点总结

考点	说明
row_number + partition by	窗口函数核心用法：按用户分组、按时间排序，为每个用户的首单打标
datediff 日期函数	计算两个日期的天数差，用于筛选注册后N天内的订单窗口
多表 join + 窗口过滤	join 关联注册和订单表后，在子查询中用 row_number 标号，外层 where rn=1 取首单
窗口函数计算占比	count(distinct user_id) / sum(count(...)) over() 在聚合结果上二次使用窗口函数计算全局占比

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t9_user_register (
    user_id       string comment '用户id',
    register_time string comment '注册时间'
) comment '用户注册表';

create table t9_order_info (
    order_id   bigint comment '订单id',
    user_id    string comment '用户id',
    category   string comment '商品品类',
    order_time string comment '下单时间'
) comment '用户订单表';

-- 插入数据
insert into t9_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 t9_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真题