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)

Friendship:
| user1_id | user2_id |
|----------|----------|
| 1        | 2        |
| 1        | 3        |
| 2        | 3        |
| 1        | 4        |
| 2        | 4        |
| 1        | 5        |
| 2        | 5        |
| 1        | 6        |
| 2        | 6        |
| 1        | 7        |
| 3        | 7        |
| 3        | 6        |

[
  {"user1_id":1,"user2_id":2,"common_friend":4},
  {"user1_id":1,"user2_id":3,"common_friend":3}
]

Friendship:
| user1_id | user2_id |
|----------|----------|
| 10       | 11       |
| 11       | 12       |
| 12       | 13       |
| 13       | 14       |
| 14       | 15       |

[]

Friendship:
| user1_id | user2_id |
|----------|----------|
| 20       | 21       |
| 20       | 22       |
| 21       | 22       |
| 20       | 23       |
| 21       | 23       |
| 20       | 24       |
| 21       | 24       |
| 20       | 25       |
| 21       | 25       |
| 22       | 23       |

[
  {"user1_id":20,"user2_id":21,"common_friend":4}
]