# $1951. 查询具有最多共同关注者的所有两两结对组
# https://leetcode-cn.com/problems/all-the-pairs-with-the-maximum-number-of-common-followers/

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

# Write your MySQL query statement below
select
    tmp.user1_id,
    tmp.user2_id
from
    (
        select
            r1.user_id as user1_id,
            r2.user_id as user2_id,
            rank() over(
                order by
                    count(*) desc
            ) rk
        from
            Relations r1,
            Relations r2
        where
            r1.user_id < r2.user_id
            and r1.follower_id = r2.follower_id
        group by
            r1.user_id,
            r2.user_id
    ) tmp
where
    tmp.rk = 1;

# clean-up
drop table Relations;