The Number of Passengers in Each Bus II - Practice Coding Problems

The Number of Passengers in Each Bus II - Problem

Database Hard

Welcome to the LeetCode Bus Station! 🚌

You're managing a busy transit hub where buses and passengers arrive throughout the day. Your job is to efficiently match passengers with buses based on their arrival times and bus capacities.

📋 The Rules:

🚌 Buses arrive with specific capacities (number of empty seats)
🧑‍🤝‍🧑 Passengers arrive at various times
⏰ A passenger can board a bus only if the passenger arrived before or at the same time as the bus
🎯 Passengers board the earliest available bus they're eligible for
🔢 If more passengers are waiting than the bus capacity, only capacity passengers can board

Your mission: Calculate how many passengers boarded each bus and return the results ordered by bus_id.

Think of it as a real-time passenger allocation system where timing and capacity constraints determine the boarding process!

Input & Output

example_1.sql — Basic Case

$ Input: Buses: | bus_id | arrival_time | capacity | |--------|--------------|----------| | 1 | 4 | 2 | | 2 | 5 | 3 | | 3 | 6 | 2 | Passengers: | passenger_id | arrival_time | |-------------|---------------| | 1 | 2 | | 2 | 1 | | 3 | 5 | | 4 | 3 |

› Output: | bus_id | passengers_cnt | |--------|----------------| | 1 | 2 | | 2 | 2 | | 3 | 0 |

💡 Note: Bus 1 (t=4): Passengers 1,2,4 are eligible (arrived ≤4). Capacity=2, so passengers 2,1 board (earliest arrivals). Bus 2 (t=5): Passengers 4,3 remain available. Both board. Bus 3 (t=6): No passengers left.

example_2.sql — High Capacity

$ Input: Buses: | bus_id | arrival_time | capacity | |--------|--------------|----------| | 1 | 3 | 10 | Passengers: | passenger_id | arrival_time | |-------------|---------------| | 1 | 1 | | 2 | 2 | | 3 | 4 |

› Output: | bus_id | passengers_cnt | |--------|----------------| | 1 | 2 |

💡 Note: Bus 1 arrives at t=3. Passengers 1(t=1) and 2(t=2) are eligible since they arrived before the bus. Passenger 3(t=4) arrived after the bus, so cannot board. Result: 2 passengers board bus 1.

example_3.sql — Empty Buses

$ Input: Buses: | bus_id | arrival_time | capacity | |--------|--------------|----------| | 1 | 2 | 5 | | 2 | 4 | 3 | Passengers: | passenger_id | arrival_time | |-------------|---------------| | 1 | 5 | | 2 | 6 |

› Output: | bus_id | passengers_cnt | |--------|----------------| | 1 | 0 | | 2 | 0 |

💡 Note: All passengers arrive after all buses have departed. Bus 1 arrives at t=2, Bus 2 at t=4, but passengers arrive at t=5 and t=6. Since passengers must arrive before or at the same time as the bus, no one can board any bus.

Visualization

Tap to expand

Understanding the Visualization

Timeline Setup

Arrange buses and passengers on a timeline by their arrival times

Eligibility Check

For each bus, identify passengers who arrived before or at bus time

First Bus Assignment

Assign each passenger to their earliest eligible bus

Capacity Control

Apply capacity limits - excess passengers wait for next bus

Final Tally

Count passengers assigned to each bus

Key Takeaway

🎯 Key Insight: Use window functions to efficiently determine each passenger's first eligible bus, then apply capacity constraints. This avoids the need for complex nested loops and provides optimal O(B log B + P log P) performance.

Time & Space Complexity

Time Complexity

⏱️

O(B log B + P log P)

Sorting buses and passengers, then single linear pass

⚡ Linearithmic

Space Complexity

O(B + P)

Only need to store final results, not intermediate cross-products

✓ Linear Space

Constraints

1 ≤ buses.length, passengers.length ≤ 10⁵
buses[i].length == 3, passengers[i].length == 2
1 ≤ bus_id, passenger_id ≤ 10⁶
1 ≤ arrival_time ≤ 10⁹
1 ≤ capacity ≤ 10⁵
All bus_id and passenger_id values are unique
No two buses arrive at the same time

Asked in

G Google 45 a Amazon 38 f Meta 25 ⊞ Microsoft 22 Apple 18 N Netflix 12

The optimal solution uses advanced SQL window functions with CTEs to efficiently assign passengers to buses. Key insight: use ROW_NUMBER() partitioned by passenger_id to find each passenger's first eligible bus, then filter by capacity constraints. Time complexity: O(B log B + P log P) for the sorting phase, then linear processing.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Subqueries)	O(B × P²)	O(B)	For each bus, manually count eligible passengers using nested queries
Window Functions with ROW_NUMBER	O(B × P log P)	O(B × P)	Use window functions to efficiently assign passengers to buses
Optimal Solution with CTE and Window Functions	O(B log B + P log P)	O(B + P)	Single-pass solution using CTEs and advanced window functions to handle passenger-bus assignment optimally

Brute Force (Nested Subqueries) — Algorithm Steps

For each bus, find all passengers who arrived <= bus arrival time
Exclude passengers already taken by earlier buses
Apply capacity limit using LIMIT clause
Count the resulting passengers for each bus

Visualization

Tap to expand

Step-by-Step Walkthrough

Select Bus

Pick the first bus to process

Scan All Passengers

Look through every passenger to find eligible ones

Check Previous Buses

For each passenger, verify they weren't taken by earlier buses

Apply Capacity

Limit passengers by bus capacity

Repeat

Do the same for every other bus

Code -

solution.c — C

// SQL Solution - Brute Force Approach
/*
SELECT 
    b.bus_id,
    MIN(b.capacity, COALESCE(passenger_count, 0)) AS passengers_cnt
FROM Buses b
LEFT JOIN (
    SELECT 
        b2.bus_id,
        COUNT(*) as passenger_count
    FROM Buses b2
    LEFT JOIN Passengers p ON p.arrival_time <= b2.arrival_time
    WHERE p.passenger_id NOT IN (
        SELECT DISTINCT p3.passenger_id
        FROM Buses b3
        JOIN Passengers p3 ON p3.arrival_time <= b3.arrival_time
        WHERE b3.arrival_time < b2.arrival_time
    )
    GROUP BY b2.bus_id
) pc ON pc.bus_id = b.bus_id
ORDER BY b.bus_id;
*/

Time & Space Complexity

Time Complexity

⏱️

O(B × P²)

For each bus (B), we potentially scan all passengers (P) multiple times

✓ Linear Growth

Space Complexity

O(B)

Result set size is proportional to number of buses

✓ Linear Space

Constraints

1 ≤ buses.length, passengers.length ≤ 10⁵
buses[i].length == 3, passengers[i].length == 2
1 ≤ bus_id, passenger_id ≤ 10⁶
1 ≤ arrival_time ≤ 10⁹
1 ≤ capacity ≤ 10⁵
All bus_id and passenger_id values are unique
No two buses arrive at the same time

43.7K Views

Medium-High Frequency

~35 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

📋 The Rules:

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Nested Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler