小红书大数据面试SQL-用户标签体系打标

⚠️ 待修正

一、题目背景

这道题来自小红书的数据分析岗面试。小红书是生活方式社区和电商，数据分析师需要从海量业务数据中挖掘洞见。

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

一、题目

小红书需要根据用户的笔记互动行为（浏览、点赞、收藏）为用户打上兴趣标签。规则如下：

• 某品类笔记浏览>=10次 → 标签"XX兴趣用户"
• 某品类笔记点赞>=5次 → 标签"XX爱好者"
• 某品类笔记收藏>=3次 → 标签"XX深度用户"

同时拥有多个等级标签时，取等级最高的标签。统计每个用户在各品类的最终标签。

假设有用户行为表 user_behavior：

+---------+----------+------------+-------+
| user_id | note_id  | action     | cnt   |
+---------+----------+------------+-------+
| u01     | N001     | view       | 8     |
| u01     | N001     | like       | 3     |
| u01     | N002     | view       | 5     |
| u01     | N002     | save       | 2     |
| u02     | N001     | view       | 12    |
| u02     | N001     | like       | 6     |
| u02     | N001     | save       | 4     |
| u03     | N003     | view       | 15    |
| u03     | N003     | like       | 4     |
+---------+----------+------------+-------+

假设 N001和N002属于"美妆"品类，N003属于"穿搭"品类。

三、思路分析

1. 需要知道每篇笔记的品类，先将笔记ID与品类建立映射（可通过子查询或CASE WHEN）；
2. 按用户和品类聚合各行为的总次数；
3. 使用 CASE WHEN 根据三级阈值打标，优先取高等级标签。

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

四、逐步推导

1.按用户和品类聚合行为次数

执行SQL

select user_id,
       category,
       sum(case when action = 'view' then cnt else 0 end) as total_view,
       sum(case when action = 'like' then cnt else 0 end) as total_like,
       sum(case when action = 'save' then cnt else 0 end) as total_save
from (
    select ub.*,
           case when ub.note_id in ('N001','N002') then '美妆'
                when ub.note_id = 'N003' then '穿搭'
                else '其他' end as category
    from user_behavior ub
) t
group by user_id, category

查询结果

+---------+----------+------------+------------+------------+
| user_id | category | total_view | total_like | total_save |
+---------+----------+------------+------------+------------+
| u01     | 美妆     | 13         | 3          | 2          |
| u02     | 美妆     | 12         | 6          | 4          |
| u03     | 穿搭     | 15         | 4          | 0          |
+---------+----------+------------+------------+------------+

2.根据规则打标

执行SQL

select user_id,
       category,
       total_view,
       total_like,
       total_save,
       case when total_save >= 3 then concat(category, '深度用户')
            when total_like >= 5 then concat(category, '爱好者')
            when total_view >= 10 then concat(category, '兴趣用户')
            else '游客' end as user_tag
from (
    select user_id,
           category,
           sum(case when action = 'view' then cnt else 0 end) as total_view,
           sum(case when action = 'like' then cnt else 0 end) as total_like,
           sum(case when action = 'save' then cnt else 0 end) as total_save
    from (
        select ub.*,
               case when ub.note_id in ('N001','N002') then '美妆'
                    when ub.note_id = 'N003' then '穿搭'
                    else '其他' end as category
        from user_behavior ub
    ) t
    group by user_id, category
) tt

查询结果

+---------+----------+------------+------------+------------+-------------+
| user_id | category | total_view | total_like | total_save | user_tag    |
+---------+----------+------------+------------+------------+-------------+
| u01     | 美妆     | 13         | 3          | 2          | 美妆兴趣用户  |
| u02     | 美妆     | 12         | 6          | 4          | 美妆深度用户  |
| u03     | 穿搭     | 15         | 4          | 0          | 穿搭兴趣用户  |
+---------+----------+------------+------------+------------+-------------+

五、常见坑点

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

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

六、举一反三

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

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

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

七、知识点总结

考点	说明
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE user_behavior (
    user_id string COMMENT '用户ID',
    note_id string COMMENT '笔记ID',
    action  string COMMENT '行为类型：view-浏览,like-点赞,save-收藏',
    cnt     int COMMENT '行为次数'
) COMMENT '用户行为表';

-- 笔记品类映射（在SQL中用CASE WHEN实现）
-- N001 -> 美妆, N002 -> 美妆, N003 -> 穿搭

-- 插入数据
insert into user_behavior(user_id, note_id, action, cnt) values
('u01','N001','view',8),
('u01','N001','like',3),
('u01','N002','view',5),
('u01','N002','save',2),
('u02','N001','view',12),
('u02','N001','like',6),
('u02','N001','save',4),
('u03','N003','view',15),
('u03','N003','like',4);

📱关注公众号

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

💬加群交流

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