The Number of Passengers in Each Bus I - Problem

You are given two tables: Buses and Passengers.

The Buses table contains information about bus arrivals at the LeetCode station:

  • bus_id (int): Unique identifier for each bus
  • arrival_time (int): Time when the bus arrives at the station

The Passengers table contains information about passenger arrivals:

  • passenger_id (int): Unique identifier for each passenger
  • arrival_time (int): Time when the passenger arrives at the station

Passenger Assignment Rules:

  • A passenger will board a bus if the passenger's arrival time is less than or equal to the bus's arrival time
  • Each passenger boards the first available bus (earliest bus they are eligible for)
  • Once a passenger boards a bus, they cannot board another bus

Write an SQL query to find the number of passengers that board each bus. Return the result ordered by bus_id in ascending order.

Table Schema

Buses
Column Name Type Description
bus_id PK int Unique identifier for each bus
arrival_time int Time when the bus arrives at the station
Primary Key: bus_id
Passengers
Column Name Type Description
passenger_id PK int Unique identifier for each passenger
arrival_time int Time when the passenger arrives at the station
Primary Key: passenger_id

Input & Output

Example 1 — Multiple passengers, different buses
Input Tables:
Buses
bus_id arrival_time
101 3
102 7
Passengers
passenger_id arrival_time
11 1
12 5
13 6
Output:
bus_id passengers_cnt
101 1
102 2
💡 Note:

Passenger 11 arrives at time 1 and can board Bus 101 (time 3) or Bus 102 (time 7), but takes the first available (Bus 101). Passengers 12 and 13 arrive at times 5 and 6 respectively, and both board Bus 102 (time 7) as it's their first eligible bus.
Example 2 — Bus with no passengers
Input Tables:
Buses
bus_id arrival_time
201 2
202 8
Passengers
passenger_id arrival_time
21 4
Output:
bus_id passengers_cnt
201 0
202 1
💡 Note:

Passenger 21 arrives at time 4, which is after Bus 201 (time 2), so they cannot board it. They board Bus 202 (time 8) instead. Bus 201 has 0 passengers.
Example 3 — All passengers on one bus
Input Tables:
Buses
bus_id arrival_time
301 10
Passengers
passenger_id arrival_time
31 2
32 4
33 7
Output:
bus_id passengers_cnt
301 3
💡 Note:

All three passengers arrive before Bus 301 (time 10), so all three passengers board this single bus.

Constraints

  • 1 ≤ bus_id ≤ 1000
  • 1 ≤ passenger_id ≤ 1000
  • 1 ≤ arrival_time ≤ 1000
  • No two buses arrive at the same time
  • Each passenger boards at most one bus

Visualization

Tap to expand
Bus Passenger Assignment ProblemInput TablesBusesbus_idarrival_time10131027Passengerspassenger_idarrival_time111125136SQL Operations1. JOIN eligible buses2. ROW_NUMBER() rankOutput Resultbus_idpassengers_cnt10111022Assignment Logic: Passenger 11 (t=1) → Bus 101 (t=3)Passengers 12,13 (t=5,6) → Bus 102 (t=7)First eligible bus assignment ensures optimal passenger distribution
Understanding the Visualization
1
Join Tables
Match passengers with eligible buses
2
Rank Buses
Find first available bus per passenger
3
Count Results
Count passengers per bus
Key Takeaway
🎯 Key Insight: Use window functions to rank buses by arrival time for each passenger, ensuring each passenger boards their earliest eligible bus

Related Problems

Asked in
Amazon 15 Microsoft 8 Google 12
25.0K Views
Medium Frequency
~20 min Avg. Time
890 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