Strong mutual friends

SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 18 minutes. Part of the Pro drill bank.

friendships records bidirectional friendships stored once where user1_id < user2_id. A pair of friends has 'strong mutual friendship' if they are direct friends AND share at least two mutual friends. Find all strong mutual friend pairs. Return user1_id, user2_id (with user1_id < user2_id), and mutual_friend_count, ordered by user1_id and user2_id ascending.

Requirements

• Output columns: user1_id, user2_id, mutual_friend_count.
• Order by user1_id ascending, user2_id ascending.

Constraints

• user1_id < user2_id in input table.
• Friendships are undirected.
• Only pairs who are already direct friends are considered.

Examples

Input: friendships user1_id,user2_id 1,2 1,3 2,3 1,4 2,4 1,5 2,5 ... Output: user1_id | user2_id | mutual_friend_count 1 | 2 | 3 Why this passes: Users 1 and 2 are direct friends and share three mutual friends (3, 4 and 5), which meets the at-least-two rule.

Input: friendships user1_id | user2_id 1 | 2 1 | 3 2 | 3 1 | 4 2 | 4 Output: user1_id | user2_id | mutual_friend_count 1 | 2 | 2 Why this passes: Users 1 and 2 are friends and share mutual friends 3 and 4 (count 2).

Topics: lakebench, sql, graph, mutual-friends.

More SQL interview questions · All interview problems · Learn data engineering