【美团】新注册骑手首周分层与时段产能留存
JOIN 连接聚合函数GROUP BYCASE WHEN日期函数用户行为分析面试真题
题目描述
来源：美团 SQL 面试真题
某即时配送平台需要对新注册骑手进行首周履约能力分层，并观察不同层级骑手在入职后第 N 周各时段的产能留存情况。
分层规则
以骑手的 reg_date 为锚点，Day0 = reg_date，首周为 Day0～Day0+6，共 7 天。
只统计 status = 'FINISHED' 的订单，按首周完成单量分层：
- 完成单量 ≥ 30：T1（金牌）。
- 完成单量 15～29：T2（银牌）。
- 完成单量 1～14：T3（铜牌）。
- 首周 0 单的骑手不进入分析。
时段规则
按 pickup_time 的小时判定：
- morning：06:00:00～10:59:59。
- noon：11:00:00～13:59:59。
- evening：17:00:00～20:59:59。
- night：其他时间，包括 00:00～05:59、14:00～16:59、21:00～23:59。
留存周规则
以 Day0 为锚点，首周仅用于定层和计算基准人数，不作为输出留存周：
- W1：Day0+7～Day0+13。
- W2：Day0+14～Day0+20。
- W4：Day0+28～Day0+34。
数据表
t_rider 骑手注册表
| 字段 | 类型 | 说明 |
|---|---|---|
| rider_id | BIGINT | 骑手 ID |
| rider_name | VARCHAR(32) | 骑手姓名 |
| city_code | VARCHAR(16) | 所在城市编码 |
| reg_date | DATE | 注册日期 |
t_delivery 配送订单流水表
| 字段 | 类型 | 说明 |
|---|---|---|
| order_id | BIGINT | 订单 ID |
| rider_id | BIGINT | 配送骑手 ID，关联 t_rider.rider_id |
| pickup_time | DATETIME | 取餐时间 |
| deliver_time | DATETIME | 送达时间，且晚于 pickup_time |
| status | VARCHAR(16) | FINISHED / CANCELLED / TIMEOUT |
问题
只统计 status = 'FINISHED' 的订单。时段只按 pickup_time 判定，订单时效为：
(UNIX_TIMESTAMP(deliver_time) - UNIX_TIMESTAMP(pickup_time)) / 60.0
输出每个分层 × 留存周 × 时段下的履约情况：
| 字段 | 说明 |
|---|---|
| tier | 层级：T1 / T2 / T3 |
| retention_week | 留存周：W1 / W2 / W4 |
| time_slot | 时段：morning / noon / evening / night |
| active_rider_cnt | 该层该周该时段至少完成 1 单的不同骑手数 |
| retention_rate | active_rider_cnt / 该层首周分层后的总骑手数 × 100，四舍五入保留 2 位 |
| avg_duration_min | 该层该周该时段所有 FINISHED 订单的平均时效分钟，四舍五入保留 2 位；无订单时为 0.00 |
必须输出所有 3 × 3 × 4 = 36 行组合。即使某组合没有订单，也要输出该组合，并将 active_rider_cnt、retention_rate、avg_duration_min 都填为 0。
排序规则
- tier 按 T1、T2、T3 升序。
- retention_week 按 W1、W2、W4 升序。
- time_slot 按 morning、noon、evening、night 的固定顺序。
- 若上述三项相同，按 active_rider_cnt 降序。
数据样例
| rider_idPKBIGINT | rider_nameVARCHAR(32) | city_codeVARCHAR(16) | reg_dateDATE |
|---|---|---|---|
| 101 | alice | BJ | 2025-01-01 |
| 201 | bob | SH | 2025-02-01 |
| 301 | cindy | GZ | 2025-03-01 |
| 401 | david | SZ | 2025-04-01 |
输入数据显示 4 / 4 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里