The First Day of the Maximum Recorded Degree in Each City — LeetCode 2314 Python Solution

Table: Weather +-------------+------+ | Column Name | Type | +-------------+------+ | city_id | int | | day | date | | degree | int | +-------------+------+ (city_id, day) is the primary key (combination of columns with unique values) for this table. Each row in this table contains the degree of the weather of a city on a certain day.

+-------------+------+
| Column Name | Type |
+-------------+------+
| city_id     | int  |
| day         | date |
| degree      | int  |
+-------------+------+
(city_id, day) is the primary key (combination of columns with unique values) for this table.
Each row in this table contains the degree of the weather of a city on a certain day.
All the degrees are recorded in the year 2022.

import duckdb
import pandas as pd

def solution(weather: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Weather", weather)
    return con.execute("""WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY city_id
                ORDER BY degree DESC, day
            ) AS rk
        FROM Weather
    )
SELECT city_id, day, degree
FROM T
WHERE rk = 1
ORDER BY 1;""").df()