Drill 2 of 4 in Social media questions
0

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…