Mutual Follows Between Active Accounts

Medium
431.

Mutual Follows Between Active Accounts

MediumJoins

MySQL

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

ColumnTypeValues
user_idint
handlevarchar
account_statusenumactive, suspended

Primary key: user_id.

One row per account.

Table: follows

ColumnType
follower_idint
followee_idint
followed_atdatetime

Primary key (together): follower_id, followee_id.

follower_id follows followee_id. Both are rows of users, and nobody follows themselves.

Example

The rows must come back in the order shown.

users

user_idhandleaccount_status
1aarav.eatsactive
2diya_travelsactive
3kabir.codesactive
4meera_fitsuspended
5rohan.memesactive

follows

follower_idfollowee_idfollowed_at
122024-11-02 10:00:00
212024-11-03 09:15:00
132024-12-01 18:20:00
342025-01-05 08:00:00
432025-01-05 08:05:00
532025-01-09 22:40:00
352025-02-14 07:30:00
252025-02-20 13:00:00

Output

handle_ahandle_b
aarav.eatsdiya_travels
kabir.codesrohan.memes
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
handlevarchar
account_statusenum(active, suspended)

Primary key: user_id.

One row per account.

Table: follows

ColumnType
follower_idint
followee_idint
followed_atdatetime

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_idhandleaccount_status
1aarav.eatsactive
2diya_travelsactive
3kabir.codesactive
4meera_fitsuspended
5rohan.memesactive

follows

follower_idfollowee_idfollowed_at
122024-11-02 10:00:00
212024-11-03 09:15:00
132024-12-01 18:20:00
342025-01-05 08:00:00
432025-01-05 08:05:00
532025-01-09 22:40:00
352025-02-14 07:30:00
252025-02-20 13:00:00

Output

handle_ahandle_b
aarav.eatsdiya_travels
kabir.codesrohan.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_id

Another 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_id

Another 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 →