High-Overlap Friendship Pairs

A social graph analytics pipeline stores friendship edges in canonical form and needs to identify unusually well-connected friend pairs.

Table: Friendship

• user1_id: one user in the friendship pair
• user2_id: the other user in the friendship pair

Data guarantees:

• (user1_id, user2_id) is unique.
• user1_id < user2_id for every row.
• Each row represents an undirected friendship relationship.

Definitions:

• Two users are mutual friends of a pair (x, y) if each user is directly connected to both x and y.
• A friendship pair is considered high-overlap when it has at least 3 mutual friends.

Task: For every friendship pair already present in the Friendship table, compute its number of mutual friends and return only the pairs whose count is at least 3.

Output requirements:

• Return exactly three columns:
  • user1_id
  • user2_id
  • common_friend
• Keep only rows with common_friend >= 3.
• Do not output duplicate friendship pairs.
• Result may be returned in any order.

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)