Status of Flight Tickets - Problem

You have two tables: Flights and Passengers.

The Flights table contains information about flight capacity:

  • flight_id: Unique identifier for each flight
  • capacity: Maximum number of passengers the flight can accommodate

The Passengers table contains booking information:

  • passenger_id: Unique identifier for each passenger
  • flight_id: The flight the passenger wants to book
  • booking_time: When the passenger made the booking

Business Rules:

  • Passengers book tickets in advance based on booking_time
  • If there are available seats when a passenger books, their ticket is confirmed
  • If the flight is at full capacity when a passenger books, they are placed on a waitlist

Write a SQL query to determine the current status (Confirmed or Waitlist) for each passenger's ticket.

Return results ordered by passenger_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 Foreign key referencing the flight
booking_time datetime Timestamp when the passenger made the booking
Primary Key: passenger_id

Input & Output

Example 1 — Mixed Confirmed and Waitlist
Input Tables:
Flights
flight_id capacity
101 2
102 1
Passengers
passenger_id flight_id booking_time
1 101 2023-01-01 10:00:00
2 101 2023-01-01 11:00:00
3 101 2023-01-01 12:00:00
4 102 2023-01-01 09:00:00
Output:
passenger_id flight_id status
1 101 Confirmed
2 101 Confirmed
3 101 Waitlist
4 102 Confirmed
💡 Note:

Flight 101 has capacity 2. Passengers 1 and 2 booked first (10:00 and 11:00) so they get Confirmed status. Passenger 3 booked at 12:00 when flight was full, so gets Waitlist. Flight 102 has capacity 1 and passenger 4 is the only one, so Confirmed.
Example 2 — All Confirmed
Input Tables:
Flights
flight_id capacity
103 3
Passengers
passenger_id flight_id booking_time
5 103 2023-01-02 14:30:00
6 103 2023-01-02 15:45:00
Output:
passenger_id flight_id status
5 103 Confirmed
6 103 Confirmed
💡 Note:

Flight 103 has capacity 3 but only 2 passengers booked. Both passengers 5 and 6 get Confirmed status since there are enough seats available.
Example 3 — All Waitlisted
Input Tables:
Flights
flight_id capacity
104 1
Passengers
passenger_id flight_id booking_time
7 104 2023-01-03 08:00:00
8 104 2023-01-03 08:30:00
9 104 2023-01-03 09:00:00
Output:
passenger_id flight_id status
7 104 Confirmed
8 104 Waitlist
9 104 Waitlist
💡 Note:

Flight 104 has capacity 1. Passenger 7 booked first at 08:00 and gets the only Confirmed seat. Passengers 8 and 9 who booked later are placed on Waitlist.

Constraints

  • 1 ≤ flight_id ≤ 1000
  • 1 ≤ capacity ≤ 100
  • 1 ≤ passenger_id ≤ 10000
  • All booking_time values are distinct
  • All passenger_id values are distinct

Visualization

Tap to expand
Flight Ticket Status Problem OverviewPassengerspassenger_idflight_idbooking_time110110:00210111:00310112:00Flightsflight_idcapacity1012JOIN + RANKby booking_timeOutput: Ticket Statuspassenger_idflight_idstatus1101Confirmed2101Confirmed3101WaitlistFirst 2 passengers get confirmed seats (capacity = 2)Later passengers go on waitlist when flight is full
Understanding the Visualization
1
Join Tables
Combine Passengers and Flights data
2
Rank Bookings
Use ROW_NUMBER() by booking time
3
Compare Status
Check rank against flight capacity
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() to simulate first-come-first-served booking queue

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
32.4K Views
Medium Frequency
~18 min Avg. Time
867 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