Skip to main content
Talentd

Status of Flight Tickets - Solution & Explanation

HardPremiumFree on FleetCode5 min read
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Self Join SimulationO(n^2)O(n)Educational approach to simulate cumulative bookings without window functions
Window Function with Running SumO(n log n)O(n)Best general solution when SQL supports window functions

Video Solution

Leetcode HARD 2793 - Status of Flight Ticket WINDOW RANK() CASE WHEN-Solved by Everyday Data Science • Everyday Data Science • 1,138 views views

Frequently Asked Questions

Is Status of Flight Tickets easy or hard?
Status of Flight Tickets is classified as a hard problem because it requires understanding ordered processing and cumulative calculations in SQL. Developers unfamiliar with window functions often struggle to express the running seat allocation cleanly.
Status of Flight Tickets Python/Java solution
This problem is typically solved using SQL because it models database operations. In Python or Java, the equivalent logic would involve sorting bookings by flight and time, maintaining a running seat counter per flight, and assigning Confirmed or Waitlist status based on capacity checks.
How to solve Status of Flight Tickets in O(n)?
Pure O(n) processing is difficult in SQL because the query engine typically sorts rows to evaluate window functions with ORDER BY. The practical solution is O(n log n) using SUM() OVER(PARTITION BY flight_id ORDER BY booking_time). This computes cumulative seat allocation and determines ticket status with a CASE condition.
What is the best approach for Status of Flight Tickets?
The best approach uses a SQL window function to compute the cumulative number of seats booked for each flight in booking order. By comparing the running total with the flight's capacity, you can mark rows as Confirmed or Waitlist using a CASE expression. This approach is concise and efficient for large datasets.
Is Status of Flight Tickets asked at Google/Amazon/Meta?
Problems involving booking systems, seat allocation, and ordered processing frequently appear in SQL interviews at large tech companies. Variants of this question test window functions, cumulative aggregates, and conditional logic in relational databases.
What data structure is used in Status of Flight Tickets?
The SQL solution relies on window functions rather than traditional data structures. Conceptually it behaves like a running prefix sum over an ordered sequence of bookings, which is similar to maintaining a cumulative counter while iterating through requests.
What is the time complexity of Status of Flight Tickets?
The optimal SQL solution runs in O(n log n) time because rows must be ordered by flight and booking time before computing the running sum. Window aggregation then processes rows sequentially within each partition. Space usage is roughly O(n) depending on the database engine's execution plan.

Ready to solve this problem?

Practice Status of Flight Tickets with our built-in code editor and test cases.

Practice on FleetCode