Tournament Winners — LeetCode 1194 Python Solution

Table: Players +-------------+-------+ | Column Name | Type | +-------------+-------+ | player_id | int | | group_id | int | +-------------+-------+ player_id is the primary key (column with unique values) of this table. Each row of this table indicates the group of each player.

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id is the primary key (column with unique values) of this table.
Each row of this table indicates the group of each player.

import duckdb
import pandas as pd

def solution(players: pd.DataFrame, matches: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Players", players)
    con.register("Matches", matches)
    return con.execute("""WITH
    s AS (
        SELECT first_player AS player_id, first_score AS score, group_id
        FROM
            Matches AS m
            JOIN Players AS p ON m.first_player = p.player_id
        UNION ALL
        SELECT second_player AS player_id, second_score AS score, group_id
        FROM
            Matches AS m
            JOIN Players AS p ON m.second_player = p.player_id
    ),
    t AS (
        SELECT group_id, player_id, SUM(score) AS scores
        FROM s
        GROUP BY player_id
    ),
    p AS (
        SELECT
            group_id,
            player_id,
            RANK() OVER (
                PARTITION BY group_id
                ORDER BY scores DESC, player_id
            ) AS rk
        FROM t
    )
SELECT group_id, player_id
FROM p
WHERE rk = 1;""").df()