SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  4. 4.  Views
  1. 1.  Best Practices for Readable and Maintainable SQL Code
  2. 2.  Writing Efficient SQL Queries
  3. 3.  Understanding Query Optimization Methods
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Explanation of the Task Find Flight Occupancy by Fare

Main Goal of the Task

The task is to calculate the average occupancy percentage of aircraft for each service class (Business and Economy) broken down by departure airports. In other words, you need to find out, on average, how full the business and economy classes were on flights departing from each airport.

Solution Logic (Step-by-Step)

  1. Step 1: Data Collection for Each Flight.

    • First, for each individual flight, we need to determine two key metrics for each service class:
      1. The total number of seats available on the aircraft (total_seats).
      2. The number of seats actually occupied (occupied_seats).
    • To do this, we need to join several tables:
      • flights - basic information about flights, including flight_id and aircraft_code.
      • seats - information about all seats on each aircraft (aircraft_code) and their service class (fare_conditions).
      • boarding_passes - information about issued boarding passes, which tells us which seats were occupied on which flight.
    • The key here is to use a LEFT JOIN for the boarding_passes table. This allows us to account for all seats on the aircraft, even those for which no boarding passes were issued (i.e., empty seats).

  2. Step 2: Calculating Occupancy for Each Flight.

    • At this stage, by grouping the data by flight_id, we can count the number of occupied seats and the total number of seats for each class.
    • Using conditional aggregation (e.g., COUNT(...) FILTER (WHERE ...)), we count the seats separately for 'Business' and 'Economy'.
    • The occupancy percentage for a single flight and a single class is calculated using the formula: (occupied_seats / total_seats) * 100.
    • It is important to handle cases where an aircraft has no seats of a certain class (e.g., no business class) to avoid division by zero.

  3. Step 3: Aggregation by Departure Airport.

    • The results from the previous step (the occupancy for each flight) now need to be grouped by the departure airport (departure_airport).
    • Using the AVG() function, we find the average occupancy percentage across all flights that departed from a given airport. This will be the final result.

Thus, the solution is built on two levels of aggregation: first by each flight, and then by each airport.

Spoiler: the SQL query with the solution is hidden below. Click to reveal. 

        with occupancy as (
            select 
                flights.flight_id, departure_airport,
                count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Business') business_occupancy,
                (count(seats.seat_no) filter (where seats.fare_conditions = 'Business'))::numeric business_seats, 
                count(boarding_passes.seat_no) filter (where seats.fare_conditions = 'Economy') economy_occupancy,
                (count(seats.seat_no) filter (where seats.fare_conditions = 'Economy'))::numeric economy_seats
            from flights
            join seats using (aircraft_code)
            left join boarding_passes on 
                boarding_passes.seat_no = seats.seat_no and 
                boarding_passes.flight_id = flights.flight_id
            where flights.actual_departure between '2017-08-01' and '2017-09-01'
            group by flights.flight_id, departure_airport
        ) select 
            departure_airport,
            (avg(case when business_seats > 0 then business_occupancy / business_seats end) * 100)::numeric(5, 2) average_business_occupancy,
            (avg(case when economy_seats  > 0 then economy_occupancy  / economy_seats  end) * 100)::numeric(5, 2) average_economy_occupancy
        from occupancy 
        group by departure_airport
        order by departure_airport;