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
Status of Flight Tickets INPUT Flights Table flight_id capacity 1 2 2 2 Passengers Table pass_id flight booking_time 101 1 2023-07-10 102 1 2023-07-10 103 1 2023-07-11 104 2 2023-07-10 105 2 2023-07-10 Flight 1: 2 seats | Flight 2: 2 seats Flight 1 Flight 2 ALGORITHM STEPS 1 Join Tables Link passengers with flights 2 Order by booking_time Earlier bookings first 3 ROW_NUMBER per flight Assign rank to each passenger 4 Compare rank vs capacity rank <= cap: Confirmed Ranking for Flight 1 Pass 101: rank=1 (cap=2) Confirmed Pass 102: rank=2 (cap=2) Confirmed Pass 103: rank=3 (cap=2) Waitlist rank > capacity = Waitlist FINAL RESULT Output Table passenger_id status 101 Confirmed 102 Confirmed 103 Waitlist 104 Confirmed 105 Confirmed Summary Confirmed: 4 passengers Waitlist: 1 passenger OK - Complete Key Insight: Use ROW_NUMBER() OVER (PARTITION BY flight_id ORDER BY booking_time) to rank passengers per flight. Compare this rank against flight capacity: if rank <= capacity then 'Confirmed', else 'Waitlist'. Window functions elegantly solve ordering problems without self-joins or subqueries. TutorialsPoint - Status of Flight Tickets | Optimal Solution

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