Mutual Follows Between Active Accounts — SQL Joins Problem
Two accounts are mutuals when each follows the other. The "close friends" feature is being rolled out to mutual pairs where both accounts are active.
- Difficulty: Medium
- Topics: Joins
- Dialect: MySQL
- Problem: #431
Problem statement
Two accounts are mutuals when each follows the other. The "close friends" feature is being rolled out to mutual pairs where both accounts are active.
Return every such pair once, with the columns handle_a and handle_b, where handle_a belongs to the account with the smaller user_id. Order the rows by the smaller user_id, then by the larger user_id. One-way follows and pairs involving a suspended account are not in the answer.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| handle | varchar |
| account_status | enum(active, suspended) |
Primary key: user_id.
One row per account.
Table: follows
| Column | Type |
|---|---|
| follower_id | int |
| followee_id | int |
| followed_at | datetime |
Primary key: follower_id, followee_id.
follower_id follows followee_id. Both are rows of users, and nobody follows themselves.
Examples
Example 1
users
| user_id | handle | account_status |
|---|---|---|
| 1 | aarav.eats | active |
| 2 | diya_travels | active |
| 3 | kabir.codes | active |
| 4 | meera_fit | suspended |
| 5 | rohan.memes | active |
follows
| follower_id | followee_id | followed_at |
|---|---|---|
| 1 | 2 | 2024-11-02 10:00:00 |
| 2 | 1 | 2024-11-03 09:15:00 |
| 1 | 3 | 2024-12-01 18:20:00 |
| 3 | 4 | 2025-01-05 08:00:00 |
| 4 | 3 | 2025-01-05 08:05:00 |
| 5 | 3 | 2025-01-09 22:40:00 |
| 3 | 5 | 2025-02-14 07:30:00 |
| 2 | 5 | 2025-02-20 13:00:00 |
Output
| handle_a | handle_b |
|---|---|
| aarav.eats | diya_travels |
| kabir.codes | rohan.memes |
How to solve Mutual Follows Between Active Accounts
A mutual follow is an edge a → b together with its reverse b → a. A self join of follows finds them: pair each row f with a row g where g.follower_id = f.followee_id and g.followee_id = f.follower_id. Every mutual pair then appears twice — once starting from each side — so the condition f.follower_id < f.followee_id keeps exactly one copy and also fixes which account is handle_a.
Joining users twice, under two aliases, brings each side's handle and status; both must be active. A pair with one suspended account is dropped even though the follows exist.
The second query starts from pairs of users instead and asks two EXISTS questions — readable, but it considers every pair of accounts. The third normalises each edge to (LEAST, GREATEST) and keeps pairs seen twice; because (follower_id, followee_id) is the primary key, a count of 2 can only mean both directions. With the composite primary key the self join is an index lookup per edge.
Reference solution (MySQL)
SELECT ua.handle AS handle_a, ub.handle AS handle_b
FROM follows f
JOIN follows g ON g.follower_id = f.followee_id AND g.followee_id = f.follower_id
JOIN users ua ON ua.user_id = f.follower_id
JOIN users ub ON ub.user_id = f.followee_id
WHERE f.follower_id < f.followee_id
AND ua.account_status = 'active'
AND ub.account_status = 'active'
ORDER BY ua.user_id, ub.user_idAnother way
SELECT ua.handle AS handle_a, ub.handle AS handle_b
FROM users ua JOIN users ub ON ua.user_id < ub.user_id
WHERE ua.account_status = 'active' AND ub.account_status = 'active'
AND EXISTS (SELECT 1 FROM follows f WHERE f.follower_id = ua.user_id AND f.followee_id = ub.user_id)
AND EXISTS (SELECT 1 FROM follows f WHERE f.follower_id = ub.user_id AND f.followee_id = ua.user_id)
ORDER BY ua.user_id, ub.user_idAnother way
SELECT ua.handle AS handle_a, ub.handle AS handle_b
FROM (SELECT LEAST(follower_id, followee_id) AS a, GREATEST(follower_id, followee_id) AS b
FROM follows GROUP BY LEAST(follower_id, followee_id), GREATEST(follower_id, followee_id)
HAVING COUNT(*) = 2) m
JOIN users ua ON ua.user_id = m.a JOIN users ub ON ub.user_id = m.b
WHERE ua.account_status = 'active' AND ub.account_status = 'active'
ORDER BY m.a, m.b← Posts With More Likes Than the Platform Average · Creator Engagement Rate by Month →