Leetcode Problem 2783. Flight Occupancy and Waitlist Analysis

2783. Flight Occupancy and Waitlist Analysis

Leetcode Solutions

SQL and Pandas Approach for Flight Booking Analysis

Perform a LEFT JOIN between the Flights and Passengers tables on flight_id to align passenger bookings with flight capacities.
Group the results by flight_id and count the number of passengers for each flight using the COUNT() function.
Use the LEAST() function to determine the confirmed bookings (booked_cnt) by taking the minimum of the flight's capacity and the passenger count.
Calculate the waitlisted passengers (waitlist_cnt) by subtracting the confirmed bookings from the total passenger count and ensuring the result is not negative using the GREATEST() function.
Order the final result by flight_id in ascending order.

erDiagram
    Flights {
        int flight_id PK
        int capacity
    }
    Passengers {
        int passenger_id PK
        int flight_id FK
    }
    Flights ||--o{ Passengers : contains

Alternative SQL Approach with Subquery for Flight Booking Analysis

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 2783. Flight Occupancy and Waitlist Analysis

Leetcode Solutions

SQL and Pandas Approach for Flight Booking Analysis

Solution Idea

Query Steps

Code Implementation

Alternative SQL Approach with Subquery for Flight Booking Analysis

Ask Question

Suggested Answer