SQLtest

📚 leçons & ✍️ pratique

Explication de la tâche Trouver l'occupation des vols par classe

Objectif principal de la tâche

L'objectif est de calculer le pourcentage moyen d'occupation des avions pour chaque classe de service (Business et Économie), ventilé par aéroport de départ. Autrement dit, il s'agit de savoir, en moyenne, à quel point les classes business et économie étaient pleines sur les vols au départ de chaque aéroport.

Logique de la solution (étape par étape)

1. Étape 1 : Collecte des données pour chaque vol

   • Pour chaque vol individuel, il faut déterminer deux métriques clés pour chaque classe :
     1. Le nombre total de sièges disponibles sur l'avion (total_seats).
     2. Le nombre de sièges effectivement occupés (occupied_seats).
   • Pour cela, il faut joindre plusieurs tables :
     • flights : informations de base sur les vols, dont flight_id et aircraft_code.
     • seats : informations sur tous les sièges de chaque avion (aircraft_code) et leur classe (fare_conditions).
     • boarding_passes : informations sur les cartes d'embarquement émises, indiquant quels sièges étaient occupés sur chaque vol.
   • Il est essentiel d'utiliser un LEFT JOIN pour la table boarding_passes afin de prendre en compte tous les sièges, même ceux sans carte d'embarquement (donc vides).

2. Étape 2 : Calcul de l'occupation pour chaque vol

   • À ce stade, en regroupant par flight_id, on peut compter le nombre de sièges occupés et le nombre total de sièges pour chaque classe.
   • Grâce à l'agrégation conditionnelle (COUNT(...) FILTER (WHERE ...)), on compte séparément les sièges pour 'Business' et 'Économie'.
   • Le pourcentage d'occupation pour un vol et une classe se calcule ainsi : (occupied_seats / total_seats) * 100.
   • Il est important de gérer les cas où un avion n'a pas de sièges d'une certaine classe (ex. pas de business) pour éviter la division par zéro.

3. Étape 3 : Agrégation par aéroport de départ

   • Les résultats précédents (occupation par vol) doivent maintenant être regroupés par aéroport de départ (departure_airport).
   • Avec la fonction AVG(), on calcule le pourcentage moyen d'occupation sur tous les vols au départ d'un aéroport donné.

La solution repose donc sur deux niveaux d'agrégation : d'abord par vol, puis par aéroport.

        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;