SQL 笔记互动率：点赞+收藏+评论/曝光量（小红书面试题）

一、题目

计算每篇笔记的互动率：(点赞数 + 收藏数 + 评论数) / 曝光量，筛选互动率 > 10% 的优质笔记，按互动率降序输出。

假设有笔记表现表 t4_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      |
+----------+-----------+-----------+----------+-----------+

二、思路分析

1. 互动率的分子是 like_cnt + save_cnt + cmt_cnt，分母是 view_cnt；
2. 直接用四则运算计算 interaction_rate；
3. 筛选互动率 > 0.1 的笔记，按互动率降序排列；
4. 考虑分母为 0 的情况，使用 nullif(view_cnt, 0) 避免除零报错。

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

三、逐步推导

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) / nullif(view_cnt, 0), 4) as interaction_rate
from t4_note_performance

查询结果

+----------+-----------+-----------+----------+-----------+--------------------+-------------------+
| note_id  | like_cnt  | save_cnt  | cmt_cnt  | view_cnt  | total_interaction  | interaction_rate  |
+----------+-----------+-----------+----------+-----------+--------------------+-------------------+
| N001     | 120       | 80        | 30       | 2000      | 230                | 0.115             |
| N002     | 50        | 20        | 10       | 3000      | 80                 | 0.0267            |
| N003     | 300       | 150       | 60       | 2500      | 510                | 0.204             |
| N004     | 10        | 5         | 2        | 500       | 17                 | 0.034             |
| N005     | 200       | 100       | 50       | 1800      | 350                | 0.1944            |
+----------+-----------+-----------+----------+-----------+--------------------+-------------------+
5 rows selected (0.36 seconds)(https://www.dwsql.com)

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) / nullif(view_cnt, 0), 4) as interaction_rate
    from t4_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.204             |
| N005     | 350                | 1800      | 0.1944            |
| N001     | 230                | 2000      | 0.115             |
+----------+--------------------+-----------+-------------------+
3 rows selected (1.083 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：互动率超过100% — 分子（点赞+收藏+评论）可能超过分母（曝光量），此时互动率>1。这可能是刷量笔记的特征（同一用户多次互动），面试中需要指出这一点并给出排查建议。

坑2：曝光量为0导致除零报错 — view_cnt = 0 时直接做除法会抛出 division by zero 异常。使用 nullif(view_cnt, 0) 将 0 转为 NULL，除法结果也为 NULL，避免报错。

坑3：小样本偏差 — 曝光量很低（如 < 100）但互动率很高的笔记，可能是数据量太小导致的统计偏差，不具备代表性。实际业务中建议加 view_cnt >= 100 的最低曝光阈值过滤。

五、知识点总结

考点	说明
多列四则运算	SELECT 中直接对多列进行加减乘除，计算衍生指标
nullif 防除零	nullif(expr, 0) 将 0 替换为 NULL，避免除零报错
round 保留小数	round(value, n) 四舍五入保留 n 位小数，提升可读性
WHERE 条件筛选	对计算结果进行阈值过滤，筛选优质内容
ORDER BY 降序	order by col desc 按指标从高到低排列

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t4_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 t4_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真题