The Number of Passengers in Each Bus II — LeetCode 2153 Python Solution

Table: Buses +--------------+------+ | Column Name | Type | +--------------+------+ | bus_id | int | | arrival_time | int | | capacity | int | +--------------+------+ bus_id contains unique values. Each row of this table contains information about the arrival time of a bus at the LeetCode station and its capacity (the number of empty seats it has).

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
| capacity     | int  |
+--------------+------+
bus_id contains unique values.
Each row of this table contains information about the arrival time of a bus at the LeetCode station and its capacity (the number of empty seats it has).
No two buses will arrive at the same time and all bus capacities will be positive integers.

import duckdb
import pandas as pd

def solution(buses: pd.DataFrame, passengers: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Buses", buses)
    con.register("Passengers", passengers)
    return con.execute("""WITH
    T AS (
        SELECT
            *,
            SUM(cnt) OVER (ORDER BY dt, bus_id) AS cur,
            IF(@t > 0, @t := cnt, @t := @t + cnt) AS cur_sum
        FROM
            (
                SELECT bus_id, arrival_time AS dt, capacity AS cnt FROM Buses
                UNION ALL
                SELECT -1, arrival_time AS dt, -1 FROM Passengers
            ) AS a JOIN (SELECT @t := 0 x) AS b
    )
SELECT
    bus_id,
    IF(cur_sum > 0, cnt - cur_sum, cnt) AS passengers_cnt
FROM T
WHERE bus_id > 0
ORDER BY bus_id;""").df()