00:00:00

Description

Editorial

Flight Booking Confirmation Status

HARD30 pts

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:
1. passenger_id
2. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Flights =

[{"flight_id":1,"capacity":2},{"flight_id":2,"capacity":2},{"flight_id":3,"capacity":1}]

Passengers =

[{"passenger_id":101,"flight_id":1,"booking_time":"2023-07-10 16:30:00"},{"passenger_id":102,"flight_id":1,"booking_time":"2023-07-10 17:45:00"},{"passenger_id":103,"flight_id":1,"booking_time":"2023-07-10 12:00:00"},{"passenger_id":104,"flight_id":2,"booking_time":"2023-07-05 13:23:00"},{"passenger_id":105,"flight_id":2,"booking_time":"2023-07-05 09:00:00"},{"passenger_id":106,"flight_id":3,"booking_time":"2023-07-08 11:10:00"},{"passenger_id":107,"flight_id":3,"booking_time":"2023-07-08 09:10:00"}]

Loading problem...

101

00:00:00

Description

Editorial

Flight Booking Confirmation Status

HARD30 pts

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:
1. passenger_id
2. 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

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

Example

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Flights =

[{"flight_id":1,"capacity":2},{"flight_id":2,"capacity":2},{"flight_id":3,"capacity":1}]

Passengers =

Flight Booking Confirmation Status

Hints

Flight Booking Confirmation Status

Hints