B站大数据面试SQL-UP主粉丝增长趋势

⚠️ 待修正

一、题目背景

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

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

一、题目

现有一张UP主粉丝表 t1_up_fans，记录每个UP主每日粉丝数。请计算每个UP主的粉丝日增长量并排名。

表 t1_up_fans：

+--------+-------------+----------+
| up_id  |    date     | fans_cnt |
+--------+-------------+----------+
| UP001  | 2023-03-01  | 10000    |
| UP001  | 2023-03-02  | 10500    |
| UP001  | 2023-03-03  | 11200    |
| UP002  | 2023-03-01  | 50000    |
| UP002  | 2023-03-02  | 50800    |
+--------+-------------+----------+

三、思路分析

使用LAG获取前一天粉丝数，计算日增量，按增量降序排名。

维度	评分
题目难度	⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

select up_id, date, fans_cnt,
       fans_cnt - lag(fans_cnt,1) over (partition by up_id order by date) as daily_growth,
       rank() over (order by fans_cnt - lag(fans_cnt,1) over (partition by up_id order by date) desc) as rn
from t1_up_fans

执行结果

+--------+-------------+----------+--------------+-----+
| up_id  |    date     | fans_cnt | daily_growth | rn  |
+--------+-------------+----------+--------------+-----+
| UP001  | 2023-03-01  | 10000    | NULL         | NULL|
| UP001  | 2023-03-02  | 10500    | 500          | 3   |
| UP001  | 2023-03-03  | 11200    | 700          | 2   |
| UP002  | 2023-03-01  | 50000    | NULL         | NULL|
| UP002  | 2023-03-02  | 50800    | 800          | 1   |
+--------+-------------+----------+--------------+-----+

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
LAG / LEAD	获取前/后一行数据，用于环比计算、状态变更检测
RANK / DENSE_RANK / ROW_NUMBER	排名函数三剑客，并列处理方式不同
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算
NULL值处理	NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t1_up_fans (up_id string, date string, fans_cnt bigint);
INSERT INTO t1_up_fans VALUES
('UP001','2023-03-01',10000),('UP001','2023-03-02',10500),
('UP001','2023-03-03',11200),('UP002','2023-03-01',50000),('UP002','2023-03-02',50800);

📱关注公众号

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

💬加群交流

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