The Number of Passengers in Each Bus I — LeetCode 2142 Python Solution

Table: Buses +--------------+------+ | Column Name | Type | +--------------+------+ | bus_id | int | | arrival_time | int | +--------------+------+ bus_id is the column with unique values for this table. Each row of this table contains information about the arrival time of a bus at the LeetCode station.

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
+--------------+------+
bus_id is the column with unique values for this table.
Each row of this table contains information about the arrival time of a bus at the LeetCode station.
No two buses will arrive at the same time.

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("""SELECT
    bus_id,
    COUNT(passenger_id) - LAG(COUNT(passenger_id), 1, 0) OVER (
        ORDER BY b.arrival_time
    ) AS passengers_cnt
FROM
    Buses AS b
    LEFT JOIN Passengers AS p ON p.arrival_time <= b.arrival_time
GROUP BY 1
ORDER BY 1;""").df()