Welcome to Subscribe On Youtube
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);