Table: Flights
+-------------+------+
| Column Name | Type |
+-------------+------+
| flight_id | int |
| capacity | int |
+-------------+------+
flight_id column contains distinct values.
Each row of this table contains flight id and capacity.
Table: Passengers
+--------------+----------+ | Column Name | Type | +--------------+----------+ | passenger_id | int | | flight_id | int | | booking_time | datetime | +--------------+----------+ passenger_id column contains distinct values. booking_time column contains distinct values. Each row of this table contains passenger id, booking time, and their flight id.
Passengers book tickets for flights in advance. If a passenger books a ticket for a flight and there are still empty seats available on the flight, the passenger's ticket will be confirmed. However, the passenger will be on a waitlist if the flight is already at full capacity.
Write a solution to determine the current status of flight tickets for each passenger.
Return the result table ordered by passenger_id in ascending order.
The result format is in the following example.
Example 1:
Input: Flights table: +-----------+----------+ | flight_id | capacity | +-----------+----------+ | 1 | 2 | | 2 | 2 | | 3 | 1 | +-----------+----------+ Passengers table: +--------------+-----------+---------------------+ | 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 | Status | +--------------+-----------+ | 101 | Confirmed | | 102 | Waitlist | | 103 | Confirmed | | 104 | Confirmed | | 105 | Confirmed | | 106 | Waitlist | | 107 | Confirmed | +--------------+-----------+ Explanation: - Flight 1 has a capacity of 2 passengers. Passenger 101 and Passenger 103 were the first to book tickets, securing the available seats. Therefore, their bookings are confirmed. However, Passenger 102 was the third person to book a ticket for this flight, which means there are no more available seats. Passenger 102 is now placed on the waitlist, - Flight 2 has a capacity of 2 passengers, Flight 2 has exactly two passengers who booked tickets, Passenger 104 and Passenger 105. Since the number of passengers who booked tickets matches the available seats, both bookings are confirmed. - Flight 3 has a capacity of 1 passenger. Passenger 107 booked earlier and secured the only available seat, confirming their booking. Passenger 106, who booked after Passenger 107, is on the waitlist.
Problem Overview: You are given flight ticket bookings and a limited seat capacity for each flight. Tickets are processed in booking order, and once the cumulative number of booked seats exceeds the available capacity, remaining passengers must be marked as waitlisted. The task is to determine the final status of each ticket.
Approach 1: Window Function with Cumulative Seat Allocation (O(n log n) time, O(n) space)
The key idea is to simulate the seat assignment order directly in SQL. Sort bookings by flight_id and booking time so tickets are processed exactly as the airline would handle them. Use a window function such as SUM(seats) OVER (PARTITION BY flight_id ORDER BY booking_time) to compute the cumulative number of seats requested up to each booking.
Once you have the cumulative seat count, compare it against the flight’s capacity. If the cumulative value is less than or equal to capacity, the ticket is Confirmed. If it exceeds capacity, the booking becomes Waitlist. This logic fits naturally inside a CASE expression.
This approach works because window functions preserve row-level detail while giving you running aggregates across a partition. Instead of writing procedural logic, SQL handles the running seat calculation efficiently during query execution.
Typical implementation steps:
1. Join ticket bookings with the flight table to access seat capacity.
2. Use a window function to compute cumulative seats for each flight ordered by booking time.
3. Apply a CASE statement to classify each ticket as Confirmed or Waitlist.
4. Return ticket identifiers along with their computed status.
The heavy lifting is done by sorting within each flight partition, which typically results in O(n log n) time complexity due to ordering. Memory overhead is minimal beyond the window aggregation buffer, giving roughly O(n) auxiliary space depending on the execution engine.
Window-based solutions are common in advanced SQL interview questions because they transform sequential allocation problems into declarative queries. If you want to strengthen related concepts, review SQL, window functions, and database query optimization.
Recommended for interviews: The window function approach is the expected solution. A brute-force simulation with self joins or iterative logic is possible but inefficient and harder to maintain. Using cumulative sums demonstrates strong SQL fundamentals and the ability to model real-world ordering constraints directly inside a query.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join Simulation | O(n^2) | O(n) | Educational approach to simulate cumulative bookings without window functions |
| Window Function with Running Sum | O(n log n) | O(n) | Best general solution when SQL supports window functions |
Leetcode HARD 2793 - Status of Flight Ticket WINDOW RANK() CASE WHEN-Solved by Everyday Data Science • Everyday Data Science • 1,055 views views
Watch 1 more video solutions →Practice Status of Flight Tickets with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor