Problem Description –
A friendship between a pair of friends x and y is strong if x and y have at least three common friends.
Write an SQL query to find all the strong friendships.
Note that the result table should not contain duplicates with user1_id < user2_id.
Return the result table in any order.
The query result format is in the following example.
Problem Link – Strong Friendship
Difficulty Level – Medium
Solution –
with f as (
select user1_id, user2_id
from Friendship
union
select user2_id user1_id, user1_id user2_id
from Friendship
)
select a.user1_id, a.user2_id, count(c.user2_id) common_friend
from Friendship a
join f b
on a.user1_id = b.user1_id # u1 friends
join f c
on a.user2_id = c.user1_id # u2 friends
and b.user2_id = c.user2_id # u1 u2 common friends
group by a.user1_id, a.user2_id
having count(c.user2_id) >= 3