SQLtest Aulas de SQL

Explicação da Tarefa Encontrar a Ocupação de Voo por Tarifa

Objetivo Principal da Tarefa

A tarefa consiste em calcular a percentagem média de ocupação das aeronaves para cada classe de serviço (Executiva e Económica) discriminada por aeroportos de partida. Por outras palavras, é necessário descobrir, em média, quão cheias estavam as classes executiva e económica nos voos que partiram de cada aeroporto.

Lógica da Solução (Passo a Passo)

1. Passo 1: Recolha de Dados para Cada Voo.

   • Primeiro, para cada voo individual, precisamos de determinar duas métricas chave para cada classe de serviço:
     1. O número total de assentos disponíveis na aeronave (total_seats).
     2. O número de assentos efetivamente ocupados (occupied_seats).
   • Para fazer isso, precisamos de juntar várias tabelas:
     • flights — informação básica sobre os voos, incluindo flight_id e aircraft_code.
     • seats — informação sobre todos os assentos em cada aeronave (aircraft_code) e a sua classe de serviço (fare_conditions).
     • boarding_passes — informação sobre os cartões de embarque emitidos, que nos diz quais assentos foram ocupados em qual voo.
   • A chave aqui é usar um LEFT JOIN para a tabela boarding_passes. Isto permite-nos contabilizar todos os assentos na aeronave, mesmo aqueles para os quais não foram emitidos cartões de embarque (ou seja, assentos vazios).

2. Passo 2: Calcular a Ocupação para Cada Voo.

   • Nesta fase, ao agrupar os dados por flight_id, podemos contar o número de assentos ocupados e o número total de assentos para cada classe.
   • Usando agregação condicional (por exemplo, COUNT(...) FILTER (WHERE ...)), contamos os assentos separadamente para 'Business' e 'Economy'.
   • A percentagem de ocupação para um único voo e uma única classe é calculada usando a fórmula: (assentos_ocupados / total_assentos) * 100.
   • É importante lidar com casos em que uma aeronave não tem assentos de uma determinada classe (por exemplo, sem classe executiva) para evitar a divisão por zero.

3. Passo 3: Agregação por Aeroporto de Partida.

   • Os resultados do passo anterior (a ocupação de cada voo) precisam agora de ser agrupados pelo aeroporto de partida (departure_airport).
   • Usando a função AVG(), encontramos a percentagem média de ocupação em todos os voos que partiram de um determinado aeroporto. Este será o resultado final.

Assim, a solução é construída em dois níveis de agregação: primeiro por cada voo e, depois, por cada aeroporto.

        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;