# $1949. 坚定的友谊
# https://leetcode-cn.com/problems/strong-friendship/

# SQL架构
Create table If Not Exists Friendship (user1_id int, user2_id int);
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
insert into Friendship (user1_id, user2_id) values ('1', '3');
insert into Friendship (user1_id, user2_id) values ('2', '3');
insert into Friendship (user1_id, user2_id) values ('1', '4');
insert into Friendship (user1_id, user2_id) values ('2', '4');
insert into Friendship (user1_id, user2_id) values ('1', '5');
insert into Friendship (user1_id, user2_id) values ('2', '5');
insert into Friendship (user1_id, user2_id) values ('1', '7');
insert into Friendship (user1_id, user2_id) values ('3', '7');
insert into Friendship (user1_id, user2_id) values ('1', '6');
insert into Friendship (user1_id, user2_id) values ('3', '6');
insert into Friendship (user1_id, user2_id) values ('2', '6');

# Write your MySQL query statement below
with tmp as (
    select
        user1_id,
        user2_id
    from
        Friendship
    union
    select
        user2_id,
        user1_id
    from
        Friendship
)
select
    id1 as user1_id,
    id2 as user2_id,
    count(*) as common_friend
from
    (
        select
            t1.user1_id as id1,
            t2.user1_id as id2
        from
            tmp t1
            join tmp t2 on t1.user2_id = t2.user2_id
        where
            t1.user1_id < t2.user1_id
        order by
            t1.user1_id,
            t2.user1_id
    ) tmp1
where
    (id1, id2) in (
        select
            *
        from
            tmp
    )
group by
    id1,
    id2
having
    count(*) >= 3
order by
    id1,
    id2;

# clean-up
drop table Friendship;