Leetcode Problem 2142. The Number of Passengers in Each Bus I

2142. The Number of Passengers in Each Bus I

Leetcode Solutions

Using Window Function LAG and LEFT JOIN

Create a Common Table Expression (CTE) named cte that selects bus_id, arrival_time, and the arrival time of the previous bus using the LAG function.
Set the default value of the LAG function to 0 to handle the first bus which has no previous bus.
Perform a LEFT JOIN between the CTE cte and the Passengers table on the condition that the passenger's arrival time is greater than the previous bus's arrival time and less than or equal to the current bus's arrival time.
Group the results by bus_id from the CTE.
Count the number of passengers for each group to get the passengers_cnt.
Order the final result by bus_id in ascending order.

erDiagram
    Buses {
        int bus_id PK
        int arrival_time
    }
    Passengers {
        int passenger_id PK
        int arrival_time
    }
    Buses ||--o{ Passengers : "has"

Using a Subquery to Determine the Minimum Bus Arrival Time for Each Passenger

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 2142. The Number of Passengers in Each Bus I

Leetcode Solutions

Using Window Function LAG and LEFT JOIN

Solution Idea

Query Steps

Code Implementation

Using a Subquery to Determine the Minimum Bus Arrival Time for Each Passenger

Ask Question

Suggested Answer