All the Pairs With the Maximum Number of Common Followers — LeetCode 1951 Python Solution

Table: Relations +-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates that the user with ID follower_id is following the user with ID user_id.

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| follower_id | int  |
+-------------+------+
(user_id, follower_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the user with ID follower_id is following the user with ID user_id.

import duckdb
import pandas as pd

def solution(relations: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Relations", relations)
    return con.execute("""WITH
    t AS (
        SELECT
            r1.user_id AS user1_id,
            r2.user_id AS user2_id,
            RANK() OVER (ORDER BY COUNT(1) DESC) AS rk
        FROM
            Relations AS r1
            JOIN Relations AS r2 ON r1.follower_id = r2.follower_id AND r1.user_id < r2.user_id
        GROUP BY r1.user_id, r2.user_id
    )
SELECT
    user1_id,
    user2_id
FROM t
WHERE rk = 1;""").df()