SQL Interview Questions – Strong Friendship

Spread the love

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

Rating: 1 out of 5.

Leave a Reply