Flight Occupancy and Waitlist Analysis - Practice Coding Problems

Flight Occupancy and Waitlist Analysis - Problem

Database Medium

You're working for an airline reservation system that needs to analyze flight bookings and manage passenger seating efficiently. The system has two main components:

📋 Database Tables

Flights Table: Contains flight information with columns flight_id (unique identifier) and capacity (maximum number of seats).

Passengers Table: Contains passenger bookings with columns passenger_id (unique identifier) and flight_id (the flight they want to book).

🎯 The Challenge

When passengers book tickets, the airline follows a simple rule: first come, first served. If there are available seats when a passenger books, they get confirmed. If the flight is already at full capacity, they go on the waitlist.

Your task: Write a SQL query to calculate for each flight:

Number of passengers with confirmed seats
Number of passengers on the waitlist

Return results ordered by flight_id in ascending order.

Input & Output

example_1.sql — Basic Scenario

$ Input: Flights table: +----------+----------+ | flight_id| capacity | +----------+----------+ | 1 | 4 | | 2 | 2 | | 3 | 6 | +----------+----------+ Passengers table: +--------------+----------+ | passenger_id | flight_id| +--------------+----------+ | 101 | 1 | | 102 | 1 | | 103 | 1 | | 104 | 2 | | 105 | 2 | | 106 | 2 | +--------------+----------+

› Output: +----------+------------+---------------+ | flight_id| booked_cnt | waitlist_cnt | +----------+------------+---------------+ | 1 | 3 | 0 | | 2 | 2 | 1 | | 3 | 0 | 0 | +----------+------------+---------------+

💡 Note: Flight 1 has capacity 4 and 3 passengers, so all 3 get seats. Flight 2 has capacity 2 and 3 passengers, so 2 get seats and 1 is waitlisted. Flight 3 has no bookings.

example_2.sql — Full Capacity

$ Input: Flights table: +----------+----------+ | flight_id| capacity | +----------+----------+ | 10 | 3 | | 20 | 1 | +----------+----------+ Passengers table: +--------------+----------+ | passenger_id | flight_id| +--------------+----------+ | 1 | 10 | | 2 | 10 | | 3 | 10 | | 4 | 10 | | 5 | 20 | +--------------+----------+

› Output: +----------+------------+---------------+ | flight_id| booked_cnt | waitlist_cnt | +----------+------------+---------------+ | 10 | 3 | 1 | | 20 | 1 | 0 | +----------+------------+---------------+

💡 Note: Flight 10: passengers 1,2,3 get seats (first come first served by passenger_id), passenger 4 waitlisted. Flight 20: passenger 5 gets the only seat.

example_3.sql — Edge Case: No Passengers

$ Input: Flights table: +----------+----------+ | flight_id| capacity | +----------+----------+ | 100 | 5 | | 200 | 3 | +----------+----------+ Passengers table: +--------------+----------+ | passenger_id | flight_id| +--------------+----------+ (empty table)

› Output: +----------+------------+---------------+ | flight_id| booked_cnt | waitlist_cnt | +----------+------------+---------------+ | 100 | 0 | 0 | | 200 | 0 | 0 | +----------+------------+---------------+

💡 Note: Both flights have no passengers, so booked and waitlist counts are 0 for all flights.

Visualization

Tap to expand

Understanding the Visualization

Passenger Arrival

Passengers book flights in order (simulated by passenger_id sequence)

Seat Assignment

First N passengers get confirmed seats where N = flight capacity

Waitlist Management

Remaining passengers are placed on waitlist for potential cancellations

Report Generation

Count and report confirmed vs waitlisted passengers per flight

Key Takeaway

🎯 Key Insight: ROW_NUMBER() window function perfectly simulates first-come-first-served booking by assigning sequence numbers, then conditional aggregation efficiently splits passengers into confirmed vs waitlisted categories.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

ROW_NUMBER() requires sorting passengers within each flight partition

⚡ Linearithmic

Space Complexity

O(n)

Space for window function results and grouping

⚡ Linearithmic Space

Constraints

1 ≤ flights.length ≤ 10⁴
1 ≤ passengers.length ≤ 10⁵
1 ≤ flight_id, passenger_id ≤ 10⁶
0 ≤ capacity ≤ 10⁴
All flight_id values in Flights table are unique
All passenger_id values in Passengers table are unique
All passenger bookings reference valid flights

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28 Apple 22 N Netflix 18

The optimal approach uses SQL window functions with ROW_NUMBER() to simulate the booking order (first-come-first-served by passenger_id). By partitioning passengers by flight and using conditional aggregation with SUM(CASE...), we efficiently count confirmed seats vs waitlisted passengers in a single query. This handles the booking sequence correctly and scales well for large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Correlated Subqueries (Brute Force)	O(n log n)	O(n)	Use nested queries to count passengers for each flight separately
Window Functions (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() to simulate booking order and efficiently categorize passengers

Correlated Subqueries (Brute Force) — Algorithm Steps

Join Flights and Passengers tables
Group by flight_id to count total passengers per flight
Use CASE statements with LEAST/GREATEST functions to split counts
Calculate booked as minimum of passenger count and capacity
Calculate waitlisted as maximum of 0 and (passenger count - capacity)

Visualization

Tap to expand

Step-by-Step Walkthrough

Count Passengers

Group passengers by flight_id and count total bookings

Apply Capacity Limit

Compare passenger count with flight capacity

Split Counts

Booked = min(passengers, capacity), Waitlist = max(0, passengers - capacity)

Code -

solution.c — C

// SQL query
const char* query = 
"SELECT "
"    f.flight_id,"
"    LEAST(COUNT(p.passenger_id), f.capacity) AS booked_cnt,"
"    GREATEST(0, COUNT(p.passenger_id) - f.capacity) AS waitlist_cnt "
"FROM Flights f "
"LEFT JOIN Passengers p ON f.flight_id = p.flight_id "
"GROUP BY f.flight_id, f.capacity "
"ORDER BY f.flight_id;";

// C implementation
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int flight_id;
    int booked_cnt;
    int waitlist_cnt;
} FlightResult;

int compare(const void *a, const void *b) {
    FlightResult *fa = (FlightResult *)a;
    FlightResult *fb = (FlightResult *)b;
    return fa->flight_id - fb->flight_id;
}

FlightResult* flightAnalysisBruteForce(int flights[][2], int flightCount, 
                                     int passengers[][2], int passengerCount, 
                                     int* resultSize) {
    FlightResult* result = malloc(flightCount * sizeof(FlightResult));
    *resultSize = flightCount;
    
    for (int i = 0; i < flightCount; i++) {
        int flightId = flights[i][0];
        int capacity = flights[i][1];
        int totalPassengers = 0;
        
        // Count passengers for this flight
        for (int j = 0; j < passengerCount; j++) {
            if (passengers[j][1] == flightId) {
                totalPassengers++;
            }
        }
        
        int bookedCnt = (totalPassengers < capacity) ? totalPassengers : capacity;
        int waitlistCnt = (totalPassengers > capacity) ? totalPassengers - capacity : 0;
        
        result[i].flight_id = flightId;
        result[i].booked_cnt = bookedCnt;
        result[i].waitlist_cnt = waitlistCnt;
    }
    
    qsort(result, flightCount, sizeof(FlightResult), compare);
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

GROUP BY requires sorting passengers by flight_id

⚡ Linearithmic

Space Complexity

O(n)

Space for grouping and intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ flights.length ≤ 10⁴
1 ≤ passengers.length ≤ 10⁵
1 ≤ flight_id, passenger_id ≤ 10⁶
0 ≤ capacity ≤ 10⁴
All flight_id values in Flights table are unique
All passenger_id values in Passengers table are unique
All passenger bookings reference valid flights

42.7K Views

Medium Frequency

~15 min Avg. Time

1.8K 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

📋 Database Tables

🎯 The Challenge

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Correlated Subqueries (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler