Meta-style Company ChallengeHardVerified answerSQLite live

Friend Suggestions (Friends of Friends)

For each user, which users do they share a mutual friend with but are not yet directly connected to?

  • CTEs
  • Joins
  • Subqueries
  • Filtering
  • Sorting

Challenge brief

Understand the request

Social Graph Team is building the 'People You May Know' feature and needs users who share a mutual friend but are not yet directly connected.

Find second-degree connections (friends of friends who are not yet direct friends) using bidirectional friendship CTEs.

Return

  • user_name (the user getting the suggestion)
  • suggested_friend (username of suggested connection)

Constraints

  • Use accepted friendships only
  • Exclude the current user and every existing direct friend
  • Return each user/suggestion pair once and order alphabetically

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

friendships

  • friendship_idINTEGER
  • user_id_1INTEGER
  • user_id_2INTEGER
  • statusVARCHAR(20)

users

  • user_idINTEGER
  • usernameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Model friendships as bidirectional using UNION ALL. Then walk two hops: user to their friend (df1), then that friend to their other friends (df2). Filter out self-suggestions and anyone already directly connected.

Hint 2

CTE direct_friends: UNION ALL of both directions WHERE accepted. Chain: df1 (me to my friend) JOIN df2 (that friend to their friends). WHERE df2.fid != df1.uid AND df2.fid NOT IN (direct friends of me).

Hint 3

Start with the tables that establish the result grain for question 23, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH direct_friends AS (SELECT user_id_1 AS uid, user_id_2 AS fid FROM friendships WHERE status = 'accepted' UNION ALL SELECT user_id_2, user_id_1 FROM friendships WHERE status = 'accepted') SELECT DISTINCT u.username AS user_name, u2.username AS suggested_friend FROM direct_friends df1 INNER JOIN direct_friends df2 ON df1.fid = df2.uid INNER JOIN users u ON df1.uid = u.user_id INNER JOIN users u2 ON df2.fid = u2.user_id WHERE df2.fid != df1.uid AND NOT EXISTS (SELECT 1 FROM direct_friends existing WHERE existing.uid = df1.uid AND existing.fid = df2.fid) ORDER BY user_name, suggested_friend

Why this works

Expand accepted friendships in both directions, traverse two hops, exclude self, and use a correlated anti-join so null semantics cannot admit an existing friend.

Success check

4 suggestions — john suggests sarah, sarah suggests john, jane suggests mike, mike suggests jane

Expected result

Use this output to verify values, aliases, ordering, and row count.

user_namesuggested_friend
jane_smithmike_wilson
john_doesarah_jones
mike_wilsonjane_smith
sarah_jonesjohn_doe

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.