DatabaseCreation: FlightCargoStatistics.sql

create materialized view FlightCargoStatistics (FlightID, FlightNumber, Origin, Destination,
    TotalLuggage, TotalLuggageWeightKg, TotalCargo, TotalCargoWeightKg) as

with LuggageSummary as (
    select t.flightid,
           count(b.id) as TotalLuggage,
           coalesce(sum(b.weight), 0) as TotalLuggageWeightKg
    from ticket t
    left join baggage b on b.ticketid = t.id
    group by t.flightid
),
CargoSummary as (
    select flightid,
           count(id) as TotalCargo,
           coalesce(sum(weight), 0) as TotalCargoWeightKg
    from additionalcargo
    group by flightid
),
Airports as (
    select f.id as FlightID, f.flightnumber,
           da.code as Origin, aa.code as Destination
    from flight f
    join gate dg on dg.id  = f.actualgatedepartureid
    join terminal dt on dt.id  = dg.terminalid
    join airport da on da.id  = dt.airportid
    join gate ag on ag.id  = f.actualgatearrivalid
    join terminal at_ on at_.id = ag.terminalid
    join airport aa  on aa.id  = at_.airportid
)
select Airports.flightid, Airports.flightnumber, Airports.origin, Airports.destination,
       coalesce(LuggageSummary.TotalLuggage, 0),
       coalesce(LuggageSummary.TotalLuggageWeightKg, 0),
       coalesce(CargoSummary.TotalCargo, 0),
       coalesce(CargoSummary.TotalCargoWeightKg, 0)
from Airports
left join LuggageSummary on LuggageSummary.flightid = Airports.flightid
left join CargoSummary   on CargoSummary.flightid = Airports.flightid;