Passenger Boarding Count Per Bus

Medium

A transit analytics team needs a deterministic boarding ledger: for every scheduled bus, report exactly how many passengers board it under first-eligible pickup rules. Table: Buses bus_id: unique identifier for a bus trip arrival_time: time when that bus arrives at the station Table: Passengers passenger_id: unique passenger identifier arrival_time: time when the passenger reaches the station Boarding policy: A passenger boards the earliest bus whose arrival_time is greater than or equal to the passenger arrival_time. A passenger can board at most one bus. If all buses arrive earlier than a passenger, that passenger is not boarded. Task: For each bus in Buses, return how many passengers board that bus. Output requirements: Return exactly two columns: bus_id passengers_cnt Return one row for every bus. Sort by bus_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Buses:
| bus_id | arrival_time |
|--------|--------------|
| 1      | 2            |
| 2      | 4            |
| 3      | 7            |

Passengers:
| passenger_id | arrival_time |
|--------------|--------------|
| 11           | 1            |
| 12           | 5            |
| 13           | 6            |
| 14           | 7            |

Output

[
  {"bus_id":1,"passengers_cnt":1},
  {"bus_id":2,"passengers_cnt":0},
  {"bus_id":3,"passengers_cnt":3}
]

Explanation

Passenger 11 boards bus 1. No passenger boards bus 2. Passengers 12, 13, and 14 all board bus 3.

Example 2

Input

Buses:
| bus_id | arrival_time |
|--------|--------------|
| 10     | 3            |
| 3      | 10           |
| 8      | 6            |

Passengers:
| passenger_id | arrival_time |
|--------------|--------------|
| 1            | 2            |
| 2            | 3            |
| 3            | 4            |
| 4            | 6            |
| 5            | 7            |
| 6            | 12           |

Output

[
  {"bus_id":3,"passengers_cnt":1},
  {"bus_id":8,"passengers_cnt":2},
  {"bus_id":10,"passengers_cnt":2}
]

Explanation

Boarding is based on arrival_time, not bus_id. Passenger 6 arrives after the final bus and is not counted.

Example 3

Input

Buses:
| bus_id | arrival_time |
|--------|--------------|
| 5      | 2            |
| 1      | 9            |

Passengers:
| passenger_id | arrival_time |
|--------------|--------------|
| (no rows)    |

Output

[
  {"bus_id":1,"passengers_cnt":0},
  {"bus_id":5,"passengers_cnt":0}
]

Explanation

With no passengers, every bus has a boarding count of zero.

Constraints

1 <= number of rows in Buses <= 600
0 <= number of rows in Passengers <= 2000
1 <= bus_id <= 2000000000
1 <= passenger_id <= 2000000000
1 <= arrival_time <= 1000000000
bus_id values are unique
Buses.arrival_time values are unique
passenger_id values are unique
A passenger boards the earliest bus with bus.arrival_time >= passenger.arrival_time
If no such bus exists, that passenger is excluded
Return columns exactly as: bus_id, passengers_cnt
Sort result by bus_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: bus_id, passengers_cnt

Buses: | bus_id | arrival_time | |--------|--------------| | 1 | 2 | | 2 | 4 | | 3 | 7 | Passengers: | passenger_id | arrival_time | |--------------|--------------| | 11 | 1 | | 12 | 5 | | 13 | 6 | | 14 | 7 |

Buses: | bus_id | arrival_time | |--------|--------------| | 10 | 3 | | 3 | 10 | | 8 | 6 | Passengers: | passenger_id | arrival_time | |--------------|--------------| | 1 | 2 | | 2 | 3 | | 3 | 4 | | 4 | 6 | | 5 | 7 | | 6 | 12 |

Buses: | bus_id | arrival_time | |--------|--------------| | 5 | 2 | | 1 | 9 | Passengers: | passenger_id | arrival_time | |--------------|--------------| | (no rows) |