B站大数据面试SQL-大会员续费率

⚠️ 待修正

一、题目背景

这道题来自B站的数据分析岗面试。B站是弹幕视频社区，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：内容创作者生态相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

现有一张大会员订阅表 t7_vip_subscription，统计到期后30天内续费的用户比例。

表 t7_vip_subscription：

+----------+-------------+-------------+----------------+
| user_id  | start_date  |  end_date   | is_auto_renew  |
+----------+-------------+-------------+----------------+
| u01      | 2023-01-01  | 2023-02-01  | 1              |
| u01      | 2023-02-05  | 2023-03-05  | 1              |
| u02      | 2023-01-01  | 2023-02-01  | 0              |
+----------+-------------+-------------+----------------+

三、思路分析

用LEAD获取用户下一次订阅的开始时间，判断是否在到期后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

执行结果

+---------------+------------+-----------+------------+
| expire_month  | expire_cnt | renew_cnt | renew_rate |
+---------------+------------+-----------+------------+
| 2023-02       | 2          | 1         | 0.5000     |
+---------------+------------+-----------+------------+

u01在到期后4天（2月5日）续费，u02未续费。

五、常见坑点

坑1：分子分母的业务定义 — 明确分子是"延迟订单数"还是"延迟用户数"，分母对应"总订单数"还是"总用户数"，口径不同结果差异大。

坑2：时间差计算的单位 — unix_timestamp 返回秒，除以60才是分钟。忘记单位转换会导致判断完全错误。

六、举一反三

1. 按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高

2. 按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比

3. 时段分析：按小时统计，识别午晚高峰时段的配送压力变化

七、知识点总结

考点	说明
LAG / LEAD	获取前/后一行数据，用于环比计算、状态变更检测
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t7_vip_subscription (
    user_id string, start_date string, end_date string, is_auto_renew int
);
INSERT INTO t7_vip_subscription VALUES
('u01','2023-01-01','2023-02-01',1),('u01','2023-02-05','2023-03-05',1),
('u02','2023-01-01','2023-02-01',0);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题