DatabaseCreation: PassengerLoyaltyProfile.sql

create materialized view PassengerLoyaltyProgram (PassengerId, Name, Surname, DateOfBirth, Email,
    MilePoints, TotalFlights, TotalSpent, MostFrequentOrigin, MostFrequentDestination) as
with FlightAirports as (
    select f.id as FlightID,
           dAirport.code as OriginCode,
           aAirport.code as DestinationCode
    from flight f
    join gate dg          on dg.id  = f.actualgatedepartureid
    join terminal dt      on dt.id  = dg.terminalid
    join airport dAirport on dAirport.id = dt.airportid
    join gate ag          on ag.id  = f.actualgatearrivalid
    join terminal at_     on at_.id = ag.terminalid
    join airport aAirport on aAirport.id = at_.airportid
),
TicketSummary as (
    select t.passengerid,
           fa.OriginCode,
           fa.DestinationCode,
           t.finalprice
    from ticket t
    join FlightAirports fa on fa.FlightID = t.flightid
),
Aggregated as (
    select passengerid,
           count(*)        as TotalFlights,
           sum(finalprice) as TotalSpent,
           mode() within group (order by OriginCode)      as MostFrequentOrigin,
           mode() within group (order by DestinationCode) as MostFrequentDestination
    from TicketSummary
    group by passengerid
)
select p.id, p.name, p.surname, p.dateofbirth,
       p.email, p.milepoints,
       coalesce(a.TotalFlights, 0),
       coalesce(a.TotalSpent, 0),
       a.MostFrequentOrigin,
       a.MostFrequentDestination
from passenger p
left join Aggregated a on a.passengerid = p.id;