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

# 1949. Strong Friendship

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);