SQLtest SQL lessons for beginners

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.

        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;