B站大数据面试SQL-视频投币/点赞/收藏比率

⚠️ 待修正

一、题目背景

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

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

一、题目

现有一张视频互动统计表 t2_video_stats，请计算每个视频的投币率、点赞率、收藏率。

比率	公式
投币率	coins / plays
点赞率	likes / plays
收藏率	favorites / plays

表 t2_video_stats：

+----------+--------+-------+-------+-----------+---------+
| video_id | plays  | likes | coins | favorites | danmaku |
+----------+--------+-------+-------+-----------+---------+
| BV001    | 100000 | 5000  | 2000  | 3000      | 800     |
| BV002    | 50000  | 3000  | 1500  | 1000      | 500     |
+----------+--------+-------+-------+-----------+---------+

三、思路分析

多列除法运算，注意使用nullif处理除数为0的情况。

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

四、逐步推导

select video_id,
       round(coins / nullif(plays,0), 4) as coin_rate,
       round(likes / nullif(plays,0), 4) as like_rate,
       round(favorites / nullif(plays,0), 4) as fav_rate
from t2_video_stats

执行结果

+----------+-----------+-----------+----------+
| video_id | coin_rate | like_rate | fav_rate |
+----------+-----------+-----------+----------+
| BV001    | 0.0200    | 0.0500    | 0.0300   |
| BV002    | 0.0300    | 0.0600    | 0.0200   |
+----------+-----------+-----------+----------+

BV002的投币率(3%)和点赞率(6%)均优于BV001。

五、常见坑点

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

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

六、举一反三

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

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

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

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t2_video_stats (
    video_id string, plays bigint, likes bigint,
    coins bigint, favorites bigint, danmaku bigint
);
INSERT INTO t2_video_stats VALUES
('BV001',100000,5000,2000,3000,800),
('BV002',50000,3000,1500,1000,500);

📱关注公众号

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

💬加群交流

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