跳到主要内容

快手大数据面试SQL-关注关系：互相关注的用户对

⚠️ 待修正

一、题目背景​

这道题来自快手社交数据团队的数据分析岗面试。快手作为短视频+社交平台，用户之间的关注关系构成了社交图谱的基础。互相关注（俗称"互关"）是两个用户之间最强的关系信号——它不仅表示单向兴趣，更代表双向互动和真实社交连接。

业务场景：快手的"可能认识的人"推荐模块需要基于互关关系做二度好友推荐。同时直播团队的"好友在看"功能、私信功能的权限控制也都依赖互关关系的判定。找出所有互关用户对是这些下游业务的基础数据。

二、题目​

已知有表t1_follow_record记录了用户之间的关注关系，包含字段：follower_id（关注者ID）、followee_id（被关注者ID）、follow_time（关注时间）。

请找出所有互相关注的用户对（即 A 关注了 B，同时 B 也关注了 A）。输出结果中每对用户只出现一次，即 (A, B) 和 (B, A) 视为同一对。要求使用自连接实现。

样例数据

+-------------+-------------+----------------------+
| follower_id | followee_id |     follow_time      |
+-------------+-------------+----------------------+
| u001        | u002        | 2024-06-01 08:00:00  |
| u002        | u001        | 2024-06-02 09:00:00  |
| u001        | u003        | 2024-06-01 10:00:00  |
| u003        | u002        | 2024-06-03 11:00:00  |
| u002        | u003        | 2024-06-04 12:00:00  |
| u004        | u001        | 2024-06-01 13:00:00  |
| u001        | u004        | 2024-06-05 14:00:00  |
| u005        | u001        | 2024-06-06 15:00:00  |
| u003        | u004        | 2024-06-02 16:00:00  |
+-------------+-------------+----------------------+

三、思路分析​

本题是社交网络关系分析中的经典问题。核心考察的是INNER JOIN自连接的理解。

核心思路：

  • 自连接匹配反向关系：将t1_follow_record与自身做INNER JOIN，连接条件为 A.follower_id = B.followee_id AND A.followee_id = B.follower_id。这样只有双向关注的关系才会匹配上。
  • 去重：自连接会产生"A关注B且B关注A"和"B关注A且A关注B"两条记录，需要只保留一条。可以通过条件 A.follower_id < A.followee_id 来只保留较小ID在前的那条。
  • 进阶：如果需要统计互关对数、互关用户数、或计算某个用户的互关好友列表，都可以基于此逻辑扩展。

在快手等社交平台中，互相关注（互关）是衡量用户社交关系的强信号，常用于好友推荐、亲密关系分析。

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

四、逐步推导​

步骤1：自连接找出所有双向关注记录​

用INNER JOIN连接关注表的两个副本，找出双向关注的用户对。注意这会返回每对互关关系的两条记录（AB和BA）。

执行SQL

select
    a.follower_id as user_a,
    a.followee_id as user_b,
    a.follow_time as a_follow_b_time,
    b.follow_time as b_follow_a_time
from t1_follow_record a
inner join t1_follow_record b
    on a.follower_id = b.followee_id
    and a.followee_id = b.follower_id

执行结果

+--------+--------+----------------------+----------------------+
| user_a | user_b |   a_follow_b_time    |   b_follow_a_time    |
+--------+--------+----------------------+----------------------+
| u001   | u002   | 2024-06-01 08:00:00  | 2024-06-02 09:00:00  |
| u002   | u001   | 2024-06-02 09:00:00  | 2024-06-01 08:00:00  |
| u001   | u004   | 2024-06-05 14:00:00  | 2024-06-01 13:00:00  |
| u004   | u001   | 2024-06-01 13:00:00  | 2024-06-05 14:00:00  |
| u002   | u003   | 2024-06-04 12:00:00  | 2024-06-03 11:00:00  |
| u003   | u002   | 2024-06-03 11:00:00  | 2024-06-04 12:00:00  |
+--------+--------+----------------------+----------------------+

步骤2：去重，每对只保留一条记录​

使用条件 a.follower_id < a.followee_id（或使用子查询 + DISTINCT）。由于自连接会产生对称的重复行，取id较小的作为user_a即可保证每对互关关系只出现一次。

执行SQL

select distinct
    a.follower_id as user_a,
    a.followee_id as user_b
from t1_follow_record a
inner join t1_follow_record b
    on a.follower_id = b.followee_id
    and a.followee_id = b.follower_id
where a.follower_id < a.followee_id

执行结果

+--------+--------+
| user_a | user_b |
+--------+--------+
| u001   | u002   |
| u001   | u004   |
| u002   | u003   |
+--------+--------+

结果：共有3对互关用户：(u001, u002), (u001, u004), (u002, u003)。

步骤3：(可选) 显示互关时间详情​

如果想了解互关双方各自是什么时候关注对方的，保留关注时间信息。

执行SQL

select
    a.follower_id as user_a,
    a.followee_id as user_b,
    a.follow_time as a_followed_b_at,
    b.follow_time as b_followed_a_at,
    case
        when a.follow_time < b.follow_time then concat(user_a, '先关注')
        when a.follow_time > b.follow_time then concat(user_b, '先关注')
        else '同时互关'
    end as remark
from t1_follow_record a
inner join t1_follow_record b
    on a.follower_id = b.followee_id
    and a.followee_id = b.follower_id
where a.follower_id < a.followee_id

执行结果

+--------+--------+----------------------+----------------------+----------------+
| user_a | user_b |   a_followed_b_at    |   b_followed_a_at    |    remark      |
+--------+--------+----------------------+----------------------+----------------+
| u001   | u002   | 2024-06-01 08:00:00  | 2024-06-02 09:00:00  | u001先关注      |
| u001   | u004   | 2024-06-05 14:00:00  | 2024-06-01 13:00:00  | u004先关注      |
| u002   | u003   | 2024-06-04 12:00:00  | 2024-06-03 11:00:00  | u003先关注      |
+--------+--------+----------------------+----------------------+----------------+

五、常见坑点​

坑1：忘记去重，返回对称重复行

自连接会将(A→B)和(B→A)两条正向记录分别作为主表的行进行匹配，结果中每对互关关系会出现两次。如果直接用于下游统计（如互关用户数），会导致重复计数。必须通过 a.follower_id < a.followee_id 或使用子查询+DISTINCT去重。

坑2：混淆 LEFT JOIN 和 INNER JOIN

如果用 LEFT JOIN 代替 INNER JOIN，会把所有关注关系都保留下来，单向关注的行中B表字段全部为NULL。这样无法区分"互关"和"单向关注"。必须用 INNER JOIN 确保只保留双向匹配的记录。

坑3：忽略大小写或空格导致匹配失败

如果用户ID在录入时有前后空格或大小写不一致（如 'U001' vs 'u001'），自连接的等值条件会匹配失败。在生产环境中需要在连接前对ID字段做 TRIM() 和 UPPER() 处理。

六、举一反三​

  1. 统计每个用户的互关好友数：基于去重后的互关结果，按 user_a 和 user_b 分别展开为两行（UNION ALL），再按用户分组 COUNT，即可得到每个用户有多少互关好友，用于社交影响力评估。

  2. 找出"互关三角"（三人互关圈）：三个用户两两互关是一个完整的小社交圈。可以在互关结果的基础上再做一次自连接：找出(A,B)互关、(B,C)互关、(A,C)互关的组合，用于社群发现。

  3. 计算互关转化率：统计所有关注关系中，有多少比例最终转化为了互关关系。即 互关对数 / 所有有向关注关系数，衡量平台的社交连接效率。

  4. 互关关系的时间窗口分析：计算从A关注B到B回关A的平均时间差（follow_back_days），分析用户的回关行为模式。间隔越短说明互动越紧密，可用于识别"强关系"好友。

七、知识点总结​

考点说明
INNER JOIN 自连接将表与自身连接，匹配反向关注关系
去重条件 a.id < b.id对同一对互关只保留ID较小者在前的一条
CASE WHEN 时序判断根据关注时间判断谁先关注，用于分析关注方向
DISTINCT vs GROUP BY在去重场景下两种方式等价，DISTINCT更简洁

八、建表语句和数据插入​

点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_follow_record (
    follower_id STRING COMMENT '关注者ID',
    followee_id STRING COMMENT '被关注者ID',
    follow_time STRING COMMENT '关注时间'
);

--数据插入
INSERT INTO t1_follow_record(follower_id, followee_id, follow_time) VALUES
('u001', 'u002', '2024-06-01 08:00:00'),
('u002', 'u001', '2024-06-02 09:00:00'),
('u001', 'u003', '2024-06-01 10:00:00'),
('u003', 'u002', '2024-06-03 11:00:00'),
('u002', 'u003', '2024-06-04 12:00:00'),
('u004', 'u001', '2024-06-01 13:00:00'),
('u001', 'u004', '2024-06-05 14:00:00'),
('u005', 'u001', '2024-06-06 15:00:00'),
('u003', 'u004', '2024-06-02 16:00:00');
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

专题
JOIN 可视化工具
相关题目
04. 直播间礼物价值Top N观众01. 最高峰同时直播人数02. 用户中两人一定认识的组合数