Medium
SQL-J05sqlMutual Friends Count
Problem
Table `friendships(user_a, user_b)` stores undirected friendships once (a < b). Return pairs (u,v) and the count of mutual friends. Output only pairs with ≥1 mutual friend, sorted by count desc.
Input
friendships(user_a, user_b)
Output
user_u, user_v, mutual_count
Constraints
1 ≤ rows ≤ 10^5
Sample input
(1,2),(1,3),(2,3),(3,4)
Sample output
(1,2)→1 (3 is mutual); (1,3)→1 (2); (2,3)→1 (1); (1,4)→1 (3); (2,4)→1 (3) ...
Explanation
A user's friends list is the union of both columns. Self-join the list to find pairs that share a friend.
joinsself-joingraph@Meta
Visible test cases
in: see prompt
out: ordered
Your solution — run it, use AI if stuck
sql