Find Peak Calling Hours for Each City — LeetCode 2984 Python Solution

Table: Calls +--------------+----------+ | Column Name | Type | +--------------+----------+ | caller_id | int | | recipient_id | int | | call_time | datetime | | city | varchar | +--------------+----------+ (caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table. Each row contains caller id, recipient id, call time, and city.

+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| caller_id    | int      |
| recipient_id | int      |
| call_time    | datetime |
| city         | varchar  |
+--------------+----------+
(caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table.
Each row contains caller id, recipient id, call time, and city.

import duckdb
import pandas as pd

def solution(calls: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Calls", calls)
    return con.execute("""WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY city
                ORDER BY cnt DESC
            ) AS rk
        FROM
            (
                SELECT
                    city,
                    HOUR(call_time) AS h,
                    COUNT(1) AS cnt
                FROM Calls
                GROUP BY 1, 2
            ) AS t
    )
SELECT city, h AS peak_calling_hour, cnt AS number_of_calls
FROM T
WHERE rk = 1
ORDER BY 2 DESC, 1 DESC;""").df()