【得物】新品系列首购到多日复购留存分析
JOIN 连接聚合函数GROUP BY窗口函数CASE WHEN日期函数用户行为分析面试真题
题目描述
来源：得物 SQL 面试真题
某潮流电商平台运营团队在做新品发售后的“首购→复购”留存分析。每当一个系列上新，运营想知道：用户在该系列的首次下单日作为 Day 0 后，在第 1、3、7、14 天当天是否产生了同一系列的复购，从而评估不同系列的长尾复购能力。
数据表
t_series 潮鞋系列字典表
| 字段 | 类型 | 说明 |
|---|---|---|
| series_id | BIGINT | 系列 ID |
| series_name | VARCHAR(64) | 系列名称 |
| launch_date | DATE | 系列首发日期（2025 年内） |
t_order 订单明细表
| 字段 | 类型 | 说明 |
|---|---|---|
| order_id | BIGINT | 订单 ID |
| user_id | BIGINT | 用户 ID |
| series_id | BIGINT | 所购商品所属系列 ID，关联 t_series.series_id |
| pay_amount | DECIMAL(10,2) | 实付金额 |
| pay_time | DATETIME | 支付时间，已剔除未支付订单 |
业务定义
对每个系列，先定位该系列的全体首购用户集：每个用户在该系列最早的一笔订单对应的下单日为该用户的 Day 0。若同一用户同一时间有多笔订单，按 order_id 较小者作为首购订单。
在首购用户中，按日历日判断同一用户、同一系列在 Day 0 之后的以下日期是否产生复购：
- D+1：Day 0 之后第 1 天。
- D+3：Day 0 之后第 3 天。
- D+7：Day 0 之后第 7 天。
- D+14：Day 0 之后第 14 天。
复购是除首购订单之外的任意一笔同系列订单。日期按 DATE(pay_time) 对齐，不是 24 小时滚动窗口；跨系列订单不计入该系列复购。
问题
输出每个有首购用户的系列的首购到复购留存矩阵：
| 字段 | 说明 |
|---|---|
| series_id | 系列 ID |
| series_name | 系列名 |
| first_buyer_cnt | 首购用户数 |
| d1_rate | Day+1 复购用户数 / 首购用户数，四舍五入保留 2 位小数 |
| d3_rate | Day+3 复购用户数 / 首购用户数，四舍五入保留 2 位小数 |
| d7_rate | Day+7 复购用户数 / 首购用户数，四舍五入保留 2 位小数 |
| d14_rate | Day+14 复购用户数 / 首购用户数，四舍五入保留 2 位小数 |
无首购用户的系列不输出。
排序规则
- d7_rate 降序。
- 若 d7_rate 相同，按 first_buyer_cnt 降序。
- 若仍相同，按 series_id 升序。
数据样例
| series_idPKBIGINT | series_nameVARCHAR(64) | launch_dateDATE |
|---|---|---|
| 1 | AJ1 Retro High | 2025-01-01 |
| 2 | NB 550 Classic | 2025-02-01 |
| 3 | Dunk Low Panda | 2025-03-01 |
| 4 | Forum Low White | 2025-04-01 |
| 5 | Air Max Dawn | 2025-05-01 |
输入数据显示 5 / 5 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里