SQL 大会员续费率：多场景续费判定与统计（B站面试题）
一、题目背景
这道题来自B站大会员业务部门的数据分析岗面试。大会员是B站的核心营收来源之一，续费率直接关系到会员收入的稳定性和LTV（用户生命周期价值）。实际业务中续费行为非常复杂：有的用户到期当天立刻续费，有的拖了几周才回来，有的连续续费多月、有的中途改变续费方式、还有的到期后再也没有回来。
业务场景：月度经营分析会上，需要按到期月份统计"30天内续费的用户比例"。分析时需要区分不同续费模式——正常续费、延迟续费、超时未续、连续续费——每种模式背后对应不同的运营策略。
二、题目
现有一张大会员订阅表 t7_vip_subscription，记录用户每次订阅的起止时间和续费方式。请按到期月份统计：到期用户数、30天内续费用户数、续费率。
表 t7_vip_subscription：
+----------+-------------+-------------+----------------+
| user_id | start_date | end_date |
+----------+-------------+-------------+
| u01 | 2023-01-01 | 2023-02-01 |
| u01 | 2023-02-05 | 2023-03-05 |
| u02 | 2023-01-01 | 2023-02-01 |
| u03 | 2023-01-15 | 2023-02-15 |
| u03 | 2023-04-01 | 2023-05-01 |
| u04 | 2023-01-01 | 2023-02-01 |
| u04 | 2023-02-01 | 2023-03-01 |
| u04 | 2023-03-01 | 2023-04-01 |
| u05 | 2023-01-10 | 2023-02-10 |
| u05 | 2023-02-28 | 2023-03-28 |
| u06 | 2023-02-01 | 2023-03-01 |
| u06 | 2023-03-01 | 2023-04-01 |
| u07 | 2023-12-01 | 2024-01-01 |
| u07 | 2024-01-10 | 2024-02-10 |
+----------+-------------+-------------+----------------+
三、思路分析
核心思路是 LEAD 获取下一条订阅 + DATEDIFF 判定续费窗口：
- 补全下次订阅：用
lead(start_date) over (partition by user_id order by start_date)获取每个用户下一次订阅的开始时间 - 判定续费窗口：计算
datediff(next_start, end_date)，如果 ≤ 30 天则算作续费 - 按月聚合：按到期月份
substr(end_date, 1, 7)分组，统计到期人数和续费人数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：用 LEAD 为每条订阅记录补全"下一次订阅开始时间"
select user_id, start_date, end_date,
lead(start_date) over (partition by user_id order by start_date) as next_start
from t7_vip_subscription
执行结果
+----------+-------------+-------------+-------------+
| user_id | start_date | end_date | next_start |
+----------+-------------+-------------+-------------+
| u01 | 2023-01-01 | 2023-02-01 | 2023-02-05 |
| u01 | 2023-02-05 | 2023-03-05 | NULL |
| u02 | 2023-01-01 | 2023-02-01 | NULL |
| u03 | 2023-01-15 | 2023-02-15 | 2023-04-01 |
| u03 | 2023-04-01 | 2023-05-01 | NULL |
| u04 | 2023-01-01 | 2023-02-01 | 2023-02-01 |
| u04 | 2023-02-01 | 2023-03-01 | 2023-03-01 |
| u04 | 2023-03-01 | 2023-04-01 | NULL |
| u05 | 2023-01-10 | 2023-02-10 | 2023-02-28 |
| u05 | 2023-02-28 | 2023-03-28 | NULL |
| u06 | 2023-02-01 | 2023-03-01 | 2023-03-01 |
| u06 | 2023-03-01 | 2023-04-01 | NULL |
| u07 | 2023-12-01 | 2024-01-01 | 2024-01-10 |
| u07 | 2024-01-10 | 2024-02-10 | NULL |
+----------+-------------+-------------+-------------+
14 rows selected (7.829 seconds)(https://www.dwsql.com)
u01的下次订阅在4天后， u03的下次订阅在45天后（超过30天阈值） u04和u06为当天续费（间隔0天） u02没有下一条记录（next_start = NULL）
步骤2：按月聚合，统计到期人数和30天内续费人数
select expire_month,
count(distinct user_id) as expire_cnt,
count(distinct case when datediff(next_start, end_date) <= 30 then user_id end) as renew_cnt,
round(count(distinct case when datediff(next_start, end_date) <= 30 then user_id end) / count(distinct user_id), 4) as renew_rate
from (
select user_id, substr(end_date, 1, 7) as expire_month, end_date,
lead(start_date) over (partition by user_id order by start_date) as next_start
from t7_vip_subscription
) t
group by expire_month
order by expire_month
执行结果
+---------------+-------------+------------+-------------+
| expire_month | expire_cnt | renew_cnt | renew_rate |
+---------------+-------------+------------+-------------+
| 2023-02 | 5 | 3 | 0.6 |
| 2023-03 | 4 | 2 | 0.5 |
| 2023-04 | 2 | 0 | 0.0 |
| 2023-05 | 1 | 0 | 0.0 |
| 2024-01 | 1 | 1 | 1.0 |
| 2024-02 | 1 | 0 | 0.0 |
+---------------+-------------+------------+-------------+
6 rows selected (1.132 seconds)(https://www.dwsql.com)
2023-02（5人到期）：u01(4天)、u04(0天)、u05(18天)续费成功；u03(45天)超时不算续费；u02终止。续费率60%。
2023-03（4人到期）：u04、u06均为当天续费成功(0天)；u01和u05为本月最后一条订阅记录无下一条。续费率50%。
2023-04（2人到期）：u04和u06均已到最后一条记录，不再续费。续费率0%。
2024-01（1人到期）：u07跨年续费成功(9天)。续费率100%（样本量为1，需注意可信度）。
五、常见坑点
坑1：同一用户多条记录导致 distinct 计数必须
每个用户可能有多个订阅周期，按月 group by 时同一个 user_id 在不同月份各有一条到期记录。如果不用 count(distinct user_id)，同一月份内一个用户到期多次（几乎不可能但需防御），或不同月份间被错误合并。这里的 distinct 确保每月内同一用户只算一人。
坑2：datediff 结果可能为负数
如果用户提前续费（新订阅 start_date < 旧订阅 end_date，如 u04 两条订阅首尾相接 2023-02-01 <= 2023-02-01），datediff(next_start, end_date) 可能为 0 甚至负数（当 start 早于 end）。<= 30 的判定包含了 0 和负数，即当天续费和提前续费都算作"30天内"。如果业务上只想统计到期后30天，需改为 datediff(next_start, end_date) between 0 and 30。
坑3：最后一条订阅的 next_start 为 NULL
每个用户最后一条订阅记录的 LEAD 返回 NULL（没有下一次了），这些记录的 datediff(null, end_date) 结果为 NULL，case when ... <= 30 条件不成立，不会被计入续费人数。这是正确的——最后一条记录无法判断是否续费。但它仍会被计入到期人数，导致续费率的分母偏大。业务上可以加 where next_start is not null 只统计"有机会续费"的记录，但这样会丢失"确定未续费"的信号，需要权衡。
六、举一反三
- 按订阅时长分析续费率差异：计算每条订阅的持续天数
datediff(end_date, start_date)，按订阅时长分段（月卡≤31天 / 季卡≤93天 / 年卡>93天），统计各时长段的续费率差异。年卡用户续费率通常高于月卡——评估是否应引导月卡用户升档 - 续费间隔分布分析：将
datediff(next_start, end_date)分段统计——0天(立即续)、1-7天(快速续)、8-30天(正常续)、31-90天(回流)、>90天/未续(流失)——这是"续费漏斗"的标准分层 - 连续续费月数统计：对每个用户，用 LEAD 链式追踪最长的"每次续费间隔 ≤ 30天"连续段，类似"最长连续登录"问题的变体。识别连续续费12个月以上的"超级会员"，用于会员等级体系设计
七、知识点总结
| 考点 | 说明 |
|---|---|
| lead + partition by user_id | 为每条订阅获取同用户的下一次订阅时间，核心的"前后关联"技巧 |
| datediff 窗口判定 | 计算到期日与续费日的间隔天数，<= 30 判定是否在有效窗口内 |
| distinct + case when 条件去重 | 防止同一用户同月多条到期记录被重复计数 |
| substr 日期截断 + group by | 从日期字段提取月份，按月聚合统计续费率趋势 |
| next_start 为 null 的处理 | 最后一条记录无"下一次"，datediff 结果 null 被条件自动排除 |
八、建表语句和数据插入
点击展开 DDL & DML
create table t7_vip_subscription (
user_id string comment '用户ID',
start_date string comment '订阅开始日期',
end_date string comment '订阅到期日期'
) comment '大会员订阅表';
insert into t7_vip_subscription values
('u01','2023-01-01','2023-02-01'),
('u01','2023-02-05','2023-03-05'),
('u02','2023-01-01','2023-02-01'),
('u03','2023-01-15','2023-02-15'),
('u03','2023-04-01','2023-05-01'),
('u04','2023-01-01','2023-02-01'),
('u04','2023-02-01','2023-03-01'),
('u04','2023-03-01','2023-04-01'),
('u05','2023-01-10','2023-02-10'),
('u05','2023-02-28','2023-03-28'),
('u06','2023-02-01','2023-03-01'),
('u06','2023-03-01','2023-04-01'),
('u07','2023-12-01','2024-01-01'),
('u07','2024-01-10','2024-02-10');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
