Interview Question Bank · Self-join on reversed pairs
2. Mutual follows
MediumSocial mediaQuestion #124
Concept: Self-join on reversed pairsshow
A follow only goes one way, so a mutual follow is two rows: A follows B and B follows A. Join the table to itself with the columns swapped, b.follower_id = a.followee_id AND b.followee_id = a.follower_id. Every mutual pair then matches twice, once from each side; keep one copy with a.follower_id < a.followee_id, which also drops anyone who follows themselves.
Example (different data):
SELECT a.sender_id, a.receiver_id FROM referrals a JOIN referrals b ON b.sender_id = a.receiver_id AND b.receiver_id = a.sender_id WHERE a.sender_id < a.receiver_id;
Your task
Murmurly wants to label mutual follows as friends in its new messaging inbox.
Return user_a, user_b and mutual_since for every pair of users who follow each other, one row per pair with user_a < user_b. mutual_since is the date of the second of the two follows, when the follow became mutual. Sort by user_a, user_b.
Starting Postgres in your browser…
Checking your access…