小红书大数据面试SQL-笔记的互动率统计
⚠️ 待修正
一、题目背景
这道题来自小红书的数据分析岗面试。小红书是生活方式社区和电商，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：数据分析相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
计算每篇笔记的互动率，互动率 = (点赞数 + 收藏数 + 评论数) / 曝光数。并找出互动率大于10%的优质笔记。
假设有笔记表现表 note_performance：
+---------+----------+----------+----------+----------+
| note_id | like_cnt | save_cnt | cmt_cnt | view_cnt |
+---------+----------+----------+----------+----------+
| N001 | 120 | 80 | 30 | 2000 |
| N002 | 50 | 20 | 10 | 3000 |
| N003 | 300 | 150 | 60 | 2500 |
| N004 | 10 | 5 | 2 | 500 |
| N005 | 200 | 100 | 50 | 1800 |
+---------+----------+----------+----------+----------+
三、思路分析
- 互动率的分子是 likes + saves + comments，分母是 views；
- 直接用四则运算计算 interaction_rate；
- 筛选互动率 > 0.1 的笔记，按互动率降序排列。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1.计算每篇笔记的互动率
执行SQL
select note_id,
like_cnt,
save_cnt,
cmt_cnt,
view_cnt,
(like_cnt + save_cnt + cmt_cnt) as total_interaction,
round((like_cnt + save_cnt + cmt_cnt) / view_cnt, 4) as interaction_rate
from note_performance
查询结果
+---------+----------+----------+---------+----------+-------------------+------------------+
| note_id | like_cnt | save_cnt | cmt_cnt | view_cnt | total_interaction | interaction_rate |
+---------+----------+----------+---------+----------+-------------------+------------------+
| N001 | 120 | 80 | 30 | 2000 | 230 | 0.1150 |
| N002 | 50 | 20 | 10 | 3000 | 80 | 0.0267 |
| N003 | 300 | 150 | 60 | 2500 | 510 | 0.2040 |
| N004 | 10 | 5 | 2 | 500 | 17 | 0.0340 |
| N005 | 200 | 100 | 50 | 1800 | 350 | 0.1944 |
+---------+----------+----------+---------+----------+-------------------+------------------+
2.筛选互动率大于10%的优质笔记
执行SQL
select note_id,
total_interaction,
view_cnt,
interaction_rate
from (
select note_id,
(like_cnt + save_cnt + cmt_cnt) as total_interaction,
view_cnt,
round((like_cnt + save_cnt + cmt_cnt) / view_cnt, 4) as interaction_rate
from note_performance
) t
where interaction_rate > 0.1
order by interaction_rate desc
查询结果
+---------+-------------------+----------+------------------+
| note_id | total_interaction | view_cnt | interaction_rate |
+---------+-------------------+----------+------------------+
| N003 | 510 | 2500 | 0.2040 |
| N005 | 350 | 1800 | 0.1944 |
| N001 | 230 | 2000 | 0.1150 |
+---------+-------------------+----------+------------------+
五、常见坑点
坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。
坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。
六、举一反三
-
按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
-
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
-
时段分析：按小时统计，识别午晚高峰时段的配送压力变化
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 条件聚合 | 灵活实现分段统计、条件计数、标签化处理 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE note_performance (
note_id string COMMENT '笔记ID',
like_cnt int COMMENT '点赞数',
save_cnt int COMMENT '收藏数',
cmt_cnt int COMMENT '评论数',
view_cnt int COMMENT '曝光数'
) COMMENT '笔记表现表';
-- 插入数据
insert into note_performance(note_id, like_cnt, save_cnt, cmt_cnt, view_cnt) values
('N001', 120, 80, 30, 2000),
('N002', 50, 20, 10, 3000),
('N003', 300, 150, 60, 2500),
('N004', 10, 5, 2, 500),
('N005', 200, 100, 50, 1800);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
