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
The Number of Passengers in Each Bus I INPUT Buses Table bus_id arrival_time 1 2 2 5 3 10 Passengers Table passenger_id arrival_time 11 1 12 1 13 5 14 6 15 7 B1 B2 B3 ALGORITHM STEPS 1 Sort Both Tables Order by arrival_time ASC 2 JOIN with Condition p.arrival <= b.arrival 3 Find First Bus MIN(bus_arrival) per passenger 4 Count Passengers GROUP BY bus_id, COUNT(*) Timeline Visualization t=1 t=2 t=5 t=7 t=10 P P P P P B1 B2 B3 FINAL RESULT Output Table bus_id passengers 1 2 2 1 3 2 Visual Summary Bus 1 P P 2 passengers Bus 2 P 1 passenger Bus 3 P P 2 passengers OK - Total: 5 passengers Key Insight: Each passenger boards the FIRST bus that arrives at or after their arrival time. Use a LEFT JOIN with subquery to find minimum bus_arrival_time for each passenger, then GROUP BY bus_id and COUNT to get passengers per bus. Order results by bus_id. TutorialsPoint - The Number of Passengers in Each Bus I | Optimal Solution

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