The Airport With the Most Traffic — LeetCode 2112 Python Solution

Table: Flights +-------------------+------+ | Column Name | Type | +-------------------+------+ | departure_airport | int | | arrival_airport | int | | flights_count | int | +-------------------+------+ (departure_airport, arrival_airport) is the primary key column (combination of columns with unique values) for this table. Each row of this table indicates that there were flights_count flights that departed from departure_airport and arrived at arrival_airport.

+-------------------+------+
| Column Name       | Type |
+-------------------+------+
| departure_airport | int  |
| arrival_airport   | int  |
| flights_count     | int  |
+-------------------+------+
(departure_airport, arrival_airport) is the primary key column (combination of columns with unique values) for this table.
Each row of this table indicates that there were flights_count flights that departed from departure_airport and arrived at arrival_airport.

import duckdb
import pandas as pd

def solution(flights: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Flights", flights)
    return con.execute("""WITH
    T AS (
        SELECT * FROM Flights
        UNION
        SELECT arrival_airport, departure_airport, flights_count FROM Flights
    ),
    P AS (
        SELECT departure_airport, SUM(flights_count) AS cnt
        FROM T
        GROUP BY 1
    )
SELECT departure_airport AS airport_id
FROM P
WHERE cnt = (SELECT MAX(cnt) FROM P);""").df()