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
Understanding the Visualization
1
Input
Flights with capacity and passenger bookings
2
Process
Assign booking order and compare with capacity
3
Output
Count confirmed vs waitlisted passengers per flight
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() to determine passenger booking order, then compare with flight capacity to classify as booked vs waitlisted
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code