Explicación de la Tarea Encontrar Ocupación de Vuelos por Tarifa

Objetivo Principal de la Tarea

La tarea consiste en calcular el porcentaje promedio de ocupación de las aeronaves para cada clase de servicio (Ejecutiva y Económica) desglosado por aeropuertos de salida. En otras palabras, necesitas averiguar, en promedio, cuán llenas estaban las clases ejecutiva y económica en los vuelos que salían de cada aeropuerto.

Lógica de la Solución (Paso a Paso)

1. Paso 1: Recolección de Datos para Cada Vuelo.

   • Primero, para cada vuelo individual, necesitamos determinar dos métricas clave para cada clase de servicio:
     1. El número total de asientos disponibles en la aeronave (total_seats).
     2. El número de asientos realmente ocupados (occupied_seats).
   • Para hacer esto, necesitamos unir varias tablas:
     • flights - información básica sobre los vuelos, incluyendo flight_id y aircraft_code.
     • seats - información sobre todos los asientos en cada aeronave (aircraft_code) y su clase de servicio (fare_conditions).
     • boarding_passes - información sobre los pases de abordar emitidos, que nos indica qué asientos estaban ocupados en qué vuelo.
   • La clave aquí es usar un LEFT JOIN para la tabla boarding_passes. Esto nos permite contabilizar todos los asientos en la aeronave, incluso aquellos para los cuales no se emitieron pases de abordar (es decir, asientos vacíos).

2. Paso 2: Cálculo de la Ocupación para Cada Vuelo.

   • En esta etapa, al agrupar los datos por flight_id, podemos contar el número de asientos ocupados y el número total de asientos para cada clase.
   • Usando agregación condicional (por ejemplo, COUNT(...) FILTER (WHERE ...)), contamos los asientos por separado para 'Ejecutiva' y 'Económica'.
   • El porcentaje de ocupación para un solo vuelo y una sola clase se calcula usando la fórmula: (occupied_seats / total_seats) * 100.
   • Es importante manejar los casos donde una aeronave no tiene asientos de una cierta clase (por ejemplo, sin clase ejecutiva) para evitar la división por cero.

3. Paso 3: Agregación por Aeropuerto de Salida.

   • Los resultados del paso anterior (la ocupación para cada vuelo) ahora deben agruparse por el aeropuerto de salida (departure_airport).
   • Usando la función AVG(), encontramos el porcentaje promedio de ocupación en todos los vuelos que salieron de un aeropuerto dado. Este será el resultado final.

Así, la solución se construye sobre dos niveles de agregación: primero por cada vuelo, y luego por cada aeropuerto.

        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;