Flight Booking Confirmation Status

Hard

An airline reservation platform needs to compute real-time ticket outcomes for each booking event. Table: Flights flight_id: unique identifier of a flight capacity: number of seats that can be confirmed Table: Passengers passenger_id: unique identifier of a passenger booking event flight_id: flight selected by this passenger booking_time: timestamp when the booking request was created Seat allocation policy: Within each flight, bookings are prioritized by booking_time ascending (earlier requests first). For a given flight, if a passenger's booking rank is less than or equal to the flight capacity, that booking is Confirmed. Otherwise, that booking is placed on Waitlist. Task: Return each passenger with their booking status. Output requirements: Return columns exactly: passenger_id Status Status values are exactly "Confirmed" or "Waitlist". Return rows ordered by passenger_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 1         | 2        |
| 2         | 2        |
| 3         | 1        |

Passengers:
| passenger_id | flight_id | booking_time         |
|--------------|-----------|----------------------|
| 101          | 1         | 2023-07-10 16:30:00  |
| 102          | 1         | 2023-07-10 17:45:00  |
| 103          | 1         | 2023-07-10 12:00:00  |
| 104          | 2         | 2023-07-05 13:23:00  |
| 105          | 2         | 2023-07-05 09:00:00  |
| 106          | 3         | 2023-07-08 11:10:00  |
| 107          | 3         | 2023-07-08 09:10:00  |

Output

[
  {"passenger_id":101,"Status":"Confirmed"},
  {"passenger_id":102,"Status":"Waitlist"},
  {"passenger_id":103,"Status":"Confirmed"},
  {"passenger_id":104,"Status":"Confirmed"},
  {"passenger_id":105,"Status":"Confirmed"},
  {"passenger_id":106,"Status":"Waitlist"},
  {"passenger_id":107,"Status":"Confirmed"}
]

Explanation

Flight 1 confirms the earliest two bookings and waitlists the third. Flight 2 has exactly two bookings for two seats. Flight 3 has one seat, so the earlier booking is confirmed and the later booking is waitlisted.

Example 2

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 10        | 1        |
| 20        | 3        |

Passengers:
| passenger_id | flight_id | booking_time         |
|--------------|-----------|----------------------|
| 1001         | 10        | 2024-01-01 08:00:00  |
| 1002         | 10        | 2024-01-01 08:00:05  |
| 2001         | 20        | 2024-02-02 09:10:00  |
| 2002         | 20        | 2024-02-02 09:11:00  |

Output

[
  {"passenger_id":1001,"Status":"Confirmed"},
  {"passenger_id":1002,"Status":"Waitlist"},
  {"passenger_id":2001,"Status":"Confirmed"},
  {"passenger_id":2002,"Status":"Confirmed"}
]

Explanation

Flight 10 can confirm only one booking. Flight 20 has capacity for all present bookings.

Example 3

Input

Flights:
| flight_id | capacity |
|-----------|----------|
| 70        | 2        |

Passengers:
| passenger_id | flight_id | booking_time         |
|--------------|-----------|----------------------|
| 7001         | 70        | 2024-04-03 07:00:00  |
| 7002         | 70        | 2024-04-03 07:05:00  |
| 7003         | 70        | 2024-04-03 07:10:00  |
| 7004         | 70        | 2024-04-03 07:20:00  |

Output

[
  {"passenger_id":7001,"Status":"Confirmed"},
  {"passenger_id":7002,"Status":"Confirmed"},
  {"passenger_id":7003,"Status":"Waitlist"},
  {"passenger_id":7004,"Status":"Waitlist"}
]

Explanation

Only the first two bookings fit into capacity. Remaining passengers are waitlisted.

Constraints

0 <= number of rows in Flights <= 250
0 <= number of rows in Passengers <= 2500
1 <= flight_id <= 1000000000
1 <= passenger_id <= 1000000000
1 <= capacity <= 100000
flight_id values are unique in Flights
passenger_id values are unique in Passengers
booking_time values are unique in Passengers
Each passenger references an existing flight_id
booking_time is a valid timestamp in YYYY-MM-DD HH:MM:SS format
Earlier booking_time has higher priority within each flight
Status = Confirmed when booking rank <= capacity; otherwise Waitlist
Return exactly columns: passenger_id, Status
Return rows ordered by passenger_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: passenger_id, Status

Flights: | flight_id | capacity | |-----------|----------| | 1 | 2 | | 2 | 2 | | 3 | 1 | Passengers: | passenger_id | flight_id | booking_time | |--------------|-----------|----------------------| | 101 | 1 | 2023-07-10 16:30:00 | | 102 | 1 | 2023-07-10 17:45:00 | | 103 | 1 | 2023-07-10 12:00:00 | | 104 | 2 | 2023-07-05 13:23:00 | | 105 | 2 | 2023-07-05 09:00:00 | | 106 | 3 | 2023-07-08 11:10:00 | | 107 | 3 | 2023-07-08 09:10:00 |

[ {"passenger_id":101,"Status":"Confirmed"}, {"passenger_id":102,"Status":"Waitlist"}, {"passenger_id":103,"Status":"Confirmed"}, {"passenger_id":104,"Status":"Confirmed"}, {"passenger_id":105,"Status":"Confirmed"}, {"passenger_id":106,"Status":"Waitlist"}, {"passenger_id":107,"Status":"Confirmed"} ]

Flights: | flight_id | capacity | |-----------|----------| | 10 | 1 | | 20 | 3 | Passengers: | passenger_id | flight_id | booking_time | |--------------|-----------|----------------------| | 1001 | 10 | 2024-01-01 08:00:00 | | 1002 | 10 | 2024-01-01 08:00:05 | | 2001 | 20 | 2024-02-02 09:10:00 | | 2002 | 20 | 2024-02-02 09:11:00 |

Flights: | flight_id | capacity | |-----------|----------| | 70 | 2 | Passengers: | passenger_id | flight_id | booking_time | |--------------|-----------|----------------------| | 7001 | 70 | 2024-04-03 07:00:00 | | 7002 | 70 | 2024-04-03 07:05:00 | | 7003 | 70 | 2024-04-03 07:10:00 | | 7004 | 70 | 2024-04-03 07:20:00 |