Interview Question Bank · Both directions of a pair · two-hop self-join · anti-join
3. People you may know
HardSocial mediaQuestion #131
Concept: Both directions of a pair · two-hop self-join · anti-joinshow
Friendships are often stored once per pair (user_a < user_b), so first unfold them into both directions with UNION ALL; after that, 'my friends' is one lookup. A friend of a friend is a two-hop self-join on that list, and the number of paths from you to someone is the number of mutual friends you share. Remove yourself and people who are already your friends before counting, with <> and NOT EXISTS.
Example (different data):
WITH links AS ( SELECT city_a AS src, city_b AS dst FROM routes UNION ALL SELECT city_b, city_a FROM routes ) SELECT l1.src, l2.dst AS two_hops_away FROM links l1 JOIN links l2 ON l2.src = l1.dst WHERE l2.dst <> l1.src;
Your task
Murmurly's 'People you may know' panel suggests friends of friends, but only when there are at least 2 mutual friends to show.
mkt_friendships stores each friendship once, with user_a < user_b. For every user, return user_id, suggested_id and mutual_friends for each person who is not the user, is not already their friend, and shares at least 2 mutual friends with them. Sort by user_id, mutual_friends highest first, then suggested_id.
Starting Postgres in your browser…
Checking your access…