How do I write a query that places passengers on buses if there is capacity?

Question

Buses and passengers arrive at a station. If a bus arrives at the station at a time tbus and a passenger arrives at a time tpassenger where tpassenger <= tbus, then the passenger will attempt to use the first available bus whose capacity has not been exceeded.

If at the moment the bus arrives at the station there are more passengers waiting than its capacity capacity, only capacity passengers will use the bus.

I want to output the users that appear on each bus (if two passengers arrive at the same time, then the passenger with the smaller passenger_id value should be given priority).

Input:

Buses table:

bus_id	arrival_time	capacity
1	2	1
2	4	10
3	7	2

Passengers table:

passenger_id	arrival_time
11	1
12	1
13	5
14	6
15	7

Output:

bus_id	capacity	b_arrival	spot	passenger_id	p_arrival
1	1	2	1	11	1
2	10	4	1	12	1
2	10	4	2	NULL	NULL
2	10	4	3	NULL	NULL
2	10	4	4	NULL	NULL
2	10	4	5	NULL	NULL
2	10	4	6	NULL	NULL
2	10	4	7	NULL	NULL
2	10	4	8	NULL	NULL
2	10	4	9	NULL	NULL
2	10	4	10	NULL	NULL
3	2	7	1	13	5
3	2	7	2	14	6

Explanation:

Passenger 11 arrives at time 1.

Passenger 12 arrives at time 1.

Bus 1 arrives at time 2 and collects passenger 11 as it has one empty seat.

Bus 2 arrives at time 4 and collects passenger 12 as it has ten empty seats.

Passenger 13 arrives at time 5.

Passenger 14 arrives at time 6.

Passenger 15 arrives at time 7.

Bus 3 arrives at time 7 and collects passengers 13 and 14 as it has two empty seats.

DDL:


create table buses(id int, arrival_time int, capacity int)
insert into buses values(1,2,1),(2,4,10),(3,7,2)
create table passengers (passenger_id int, arrival_time int)
insert into passengers values(11,1),(12,1),(13,5),(14,6),(15,7)

Joe Obbish · Accepted Answer · 2023-01-03T20:32:56.500

One way to solve this is with the following algorithm:

Enumerate the passengers in priority order.
Generate a row for each bus spot and enumerate all rows in priority order.
For the first passenger, find the first available bus spot and keep track of the bus spot that was used.
For each subsequent passenger, find the first available bus spot after the bus spot that was taken by the previous passenger.

That algorithm can be implemented using a recursive CTE. For example:

WITH numbers_table AS (
    SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num -- will a bus have more than 1000 seats?
    FROM master..spt_values
),
enumerated_passengers AS (
    SELECT p.passenger_id, p.arrival_time AS p_arrival, ROW_NUMBER() OVER (ORDER BY p.arrival_time, p.passenger_id) p_key
    FROM passengers p
),
enumerated_bus_spots AS (
    SELECT b.id bus_id, b.capacity, b.arrival_time AS b_arrival, nums.num AS Spot, ROW_NUMBER() OVER (ORDER BY b.arrival_time, b.id, nums.num) bs_key
    FROM buses b
    INNER JOIN numbers_table nums ON nums.num <= CAST(b.capacity AS BIGINT)
),
boarded_passengers AS (
    SELECT TOP (1) p.passenger_id, p.p_arrival, p.p_key, bs.bs_key Last_Filled_Bus_Spot
    FROM enumerated_passengers p
    INNER JOIN enumerated_bus_spots bs ON p.p_arrival <= bs.b_arrival
    WHERE p.p_key = 1
    ORDER BY bs.bs_key ASC
UNION ALL

SELECT q.passenger_id, q.p_arrival, q.p_key, q.bs_key Last_Filled_Bus_Spot
FROM
(
    SELECT p.passenger_id, p.p_arrival, p.p_key, bs.bs_key, ROW_NUMBER() OVER (ORDER BY bs.bs_key) RN_temp
    FROM enumerated_passengers p
    INNER JOIN enumerated_bus_spots bs ON p.p_arrival &lt;= bs.b_arrival
    INNER JOIN boarded_passengers c ON p.p_key = c.p_key + 1
    WHERE bs.bs_key &gt; c.Last_Filled_Bus_Spot
) q
WHERE q.RN_temp = 1

)
SELECT bs.bus_id, bs.capacity, bs.b_arrival, bs.spot, bp.passenger_id, bp.p_arrival
FROM enumerated_bus_spots bs
LEFT OUTER JOIN boarded_passengers bp ON bs.bs_key = bp.Last_Filled_Bus_Spot
ORDER BY bs.b_arrival, bs.bus_id, bs.spot;

I get the desired output for your sample data:

As implemented, this approach is not likely to perform well for large tables. One way to improve performance for larger tables would be to write some of the CTEs to temp tables with appropriate indexes.

How do I write a query that places passengers on buses if there is capacity?

1 Answers1