Flight Occupancy and Waitlist Analysis - Problem

You are given two tables: Flights and Passengers. Each flight has a fixed capacity, and passengers book tickets in advance.

When a passenger books a ticket:

  • If there are still empty seats available, the passenger gets a confirmed seat
  • If the flight is already at full capacity, the passenger goes on the waitlist

Write a SQL query to report the number of passengers who successfully booked a flight (got a seat) and the number of passengers who are on the waitlist for each flight.

Return the result table ordered by flight_id in ascending order.

Table Schema

Flights
Column Name Type Description
flight_id PK int Unique identifier for each flight
capacity int Maximum number of passengers the flight can accommodate
Primary Key: flight_id
Passengers
Column Name Type Description
passenger_id PK int Unique identifier for each passenger
flight_id int ID of the flight the passenger booked
Primary Key: passenger_id

Input & Output

Example 1 — Flight with Mixed Bookings
Input Tables:
Flights
flight_id capacity
1 4
2 1
Passengers
passenger_id flight_id
101 1
102 1
103 1
104 1
105 1
106 2
Output:
flight_id booked_cnt waitlist_cnt
1 4 1
2 1 0
💡 Note:

Flight 1 has capacity 4. First 4 passengers (101-104) get confirmed seats, passenger 105 goes to waitlist. Flight 2 has capacity 1 and only passenger 106 books, so they get confirmed.
Example 2 — Flight with No Bookings
Input Tables:
Flights
flight_id capacity
3 3
Passengers
passenger_id flight_id
Output:
flight_id booked_cnt waitlist_cnt
3 0 0
💡 Note:

Flight 3 has no passenger bookings, so both booked_cnt and waitlist_cnt are 0. The LEFT JOIN ensures flights without passengers are still included in results.

Constraints

  • 1 ≤ flight_id ≤ 1000
  • 1 ≤ capacity ≤ 1000
  • 1 ≤ passenger_id ≤ 10000
  • Each passenger books exactly one flight
  • Passengers are processed in order of passenger_id

Visualization

Tap to expand
Flight Occupancy and Waitlist Analysis INPUT Flights Table flight_id capacity 101 2 102 3 Passengers Table p_id f_id booking 1 101 2024-01-01 2 101 2024-01-02 3 101 2024-01-03 4 102 2024-01-01 5 102 2024-01-02 ALGORITHM STEPS 1 Sort Passengers Order by booking date per flight 2 Use ROW_NUMBER() Assign row num within each flight 3 Compare with Capacity row_num <= capacity = Confirmed 4 Count by Status Group by flight, count each status Flight 101 Cap: 2 Flight 102 Cap: 3 P1 P2 P3 Conf Conf Wait P4 P5 Conf Conf FINAL RESULT flight_id confirmed wait 101 2 1 102 2 0 Summary Flight 101 2 1 Conf Wait Flight 102 2 0 Conf Wait OK - All passengers processed Confirmed Waitlisted Key Insight: Using ROW_NUMBER() with PARTITION BY flight_id and ORDER BY booking_date assigns sequential numbers to passengers per flight. Comparing row number with capacity determines confirmed vs waitlisted status. Time Complexity: O(n log n) for sorting | Space Complexity: O(n) for window function processing. TutorialsPoint - Flight Occupancy and Waitlist Analysis | Optimal Solution

Related Problems

Asked in
Airbnb 8 Expedia 12
23.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen