Formatted question description: https://leetcode.ca/all/1949.html

1949. Strong Friendship

Level

Medium

Description

Table: Friendship

+-------------+------+
| Column Name | Type |
+-------------+------+
| user1_id    | int  |
| user2_id    | int  |
+-------------+------+
(user1_id, user2_id) is the primary key for this table.
Each row of this table indicates that the users user1_id and user2_id are friends.
Note that user1_id < user2_id.

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:

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

Result table:
+----------+----------+---------------+
| user1_id | user2_id | common_friend |
+----------+----------+---------------+
| 1        | 2        | 4             |
| 1        | 3        | 3             |
+----------+----------+---------------+
Users 1 and 2 have 4 common friends (3, 4, 5, and 6).
Users 1 and 3 have 3 common friends (2, 6, and 7).
We did not include the friendship of users 2 and 3 because they only have two common friends (1 and 6).

Solution

Use union all to generate the table. Use join and group by to filter the entries that satisfy all requirements.

# Write your MySQL query statement below
with t as (
    select * from Friendship 
    union all 
    select user2_id, user1_id from Friendship 
    order by user1_id, user2_id
)
select distinct * from
    (select t1.user1_id, t2.user1_id as user2_id, count(t2.user1_id) as common_friend
    from t as t1 join t as t2 
    on t1.user1_id != t2.user1_id and t1.user2_id = t2.user2_id
    group by t1.user1_id, t2.user1_id
    having count(t2.user1_id) >= 3) as a
where (user1_id, user2_id) in (select * from Friendship);

All Problems

All Solutions