Pairs who follow each other

Easy

Question

In a social app, the follows table has one row each time a user follows another user: follower_id follows followee_id. A user follows another user at most once.

Two users are mutual follows when each of them follows the other. Find all pairs of mutual follows.

Return user_a and user_b, with the smaller id in user_a. Each pair must appear once. A user who follows themselves does not make a pair. Sort by user_a, then by user_b.

The order of the rows matters for this question.

self join
join
exists
pairs
social graph

Read first: Lesson 44, SQL joins: inner, left, anti-join and self-join

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

follows2 columns · 13 rowsnot run yet

CREATE TABLE follows (follower_id INTEGER NOT NULL, followee_id INTEGER NOT NULL, PRIMARY KEY (follower_id, followee_id));

Example rows of the follows table
follower_id	followee_id
1	2
2	1
1	3
3	4
4	3
2	5
5	2
5	1
6	1
7	7
4	6
3	1
1	5

About this SQL

A self join uses the same table twice with two aliases (a and b). To find a pair in both directions, join the table to itself with the two columns crossed. Writing the smaller id first (a < b) is the usual way to list an unordered pair once. This works the same in all databases.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready