Drill 3 of 4 in Social media questions
0

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…