【网易】按首发时段统计歌手新歌听众回流
JOIN 连接聚合函数GROUP BYCASE WHEN日期函数用户行为分析面试真题
题目描述
来源：网易 SQL 面试真题
某音乐流媒体平台希望分析独立音乐人新歌发布效果：
每当一首新歌在某位歌手的主页首发上线，团队会关注首发当日完整听完该新歌的听众，在接下来的 D+1、D+3、D+7 日是否还在该歌手的任意歌曲下继续产生完整收听。
数据表
t_song 歌曲表
| 字段 | 类型 | 说明 |
|---|---|---|
| song_id | BIGINT | 歌曲 ID |
| artist_id | BIGINT | 歌手 ID |
| song_name | VARCHAR(64) | 歌曲名 |
| duration_sec | INT | 歌曲总时长（秒） |
| release_time | DATETIME | 首发上线时间 |
t_play 收听流水表
| 字段 | 类型 | 说明 |
|---|---|---|
| play_id | BIGINT | 收听记录 ID |
| user_id | BIGINT | 用户 ID |
| song_id | BIGINT | 歌曲 ID，关联 t_song.song_id |
| play_start | DATETIME | 开始收听时间 |
| play_sec | INT | 实际收听秒数 |
业务规则
首发完听用户
对每首新歌，首发日为 DATE(release_time)。定位首发日当天收听该新歌的用户：
- 至少有一条 play_sec ≥ duration_sec 的记录。
- 同一用户对同一首新歌只形成一个“用户 × 新歌”组合。
- 同一用户在多首新歌的首发日都完听时，可以分别计入各首新歌和歌手的基数。
首发时段
对每个“首发完听用户 × 新歌”组合，取该用户当天对该新歌的最早 play_start：
- morning：06:00:00～11:59:59
- afternoon：12:00:00～17:59:59
- night：18:00:00～23:59:59 或 00:00:00～05:59:59
回流判定
回流是指该用户在目标日期当天，对同一歌手的任意歌曲产生至少一次完整收听：
- D+1：首发日之后 1 个日历日
- D+3：首发日之后 3 个日历日
- D+7：首发日之后 7 个日历日
同一歌手同日有多首新歌首发时，各首新歌分别判定；同一用户可以被多次计入基数。
输出要求
输出歌手 × 首发时段的回流矩阵，仅保留 base_user_cnt > 0 的行。
| 字段 | 说明 |
|---|---|
| artist_id | 歌手 ID |
| time_slot | morning / afternoon / night |
| base_user_cnt | 该歌手该时段的首发完听用户基数，即用户 × 新歌组合数 |
| d1_rate | D+1 回流率，四舍五入保留 2 位小数 |
| d3_rate | D+3 回流率，四舍五入保留 2 位小数 |
| d7_rate | D+7 回流率，四舍五入保留 2 位小数 |
排序规则
- d7_rate 降序。
- base_user_cnt 降序。
- artist_id 升序。
- time_slot 按 morning、afternoon、night 的固定顺序。
数据样例
| song_idPKBIGINT | artist_idBIGINT | song_nameVARCHAR(64) | duration_secINT | release_timeDATETIME |
|---|---|---|---|---|
| 101 | 1 | new dawn | 200 | 2025-03-01 09:00:00 |
| 102 | 1 | second light | 180 | 2025-03-01 17:00:00 |
| 103 | 1 | catalog track a | 240 | 2025-01-15 10:00:00 |
| 201 | 2 | ocean signal | 300 | 2025-03-05 13:00:00 |
| 202 | 2 | deep current | 240 | 2025-03-05 18:00:00 |
| 203 | 2 | catalog track b | 220 | 2025-01-10 10:00:00 |
输入数据显示 6 / 6 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里