The Change in Global Rankings — LeetCode 2175 Python Solution

Table: TeamPoints +-------------+---------+ | Column Name | Type | +-------------+---------+ | team_id | int | | name | varchar | | points | int | +-------------+---------+ team_id contains unique values. Each row of this table contains the ID of a national team, the name of the country it represents, and the points it has in the global rankings.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| team_id     | int     |
| name        | varchar |
| points      | int     |
+-------------+---------+
team_id contains unique values.
Each row of this table contains the ID of a national team, the name of the country it represents, and the points it has in the global rankings. No two teams will represent the same country.

import duckdb
import pandas as pd

def solution(team_points: pd.DataFrame, points_change: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("TeamPoints", team_points)
    con.register("PointsChange", points_change)
    return con.execute("""WITH
    P AS (
        SELECT team_id, SUM(points_change) AS delta
        FROM PointsChange
        GROUP BY team_id
    )
SELECT
    team_id,
    name,
    CAST(RANK() OVER (ORDER BY points DESC, name) AS SIGNED) - CAST(
        RANK() OVER (ORDER BY (points + delta) DESC, name) AS SIGNED
    ) AS 'rank_diff'
FROM
    TeamPoints
    JOIN P USING (team_id);""").df()