Flights Extended

WITH codeshares_row_order as (
SELECT co.flight_id
, co.carrier_id
, co.flight_number
, co.relationship
, row_number() over (
partition by co.flight_id
order by
co.carrier_id
) as codeshare_no
FROM codeshares as co
),

codeshares_pivot as (
SELECT cro.flight_id
, LISTAGG(CASE WHEN cro.codeshare_no = 1 THEN cro.carrier_id ELSE NULL END) as codeshare1_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 1 THEN cro.flight_number ELSE NULL END) as codeshare1_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 1 THEN cro.relationship ELSE NULL END) as codeshare1_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 2 THEN cro.carrier_id ELSE NULL END) as codeshare2_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 2 THEN cro.flight_number ELSE NULL END) as codeshare2_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 2 THEN cro.relationship ELSE NULL END) as codeshare2_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 3 THEN cro.carrier_id ELSE NULL END) as codeshare3_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 3 THEN cro.flight_number ELSE NULL END) as codeshare3_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 3 THEN cro.relationship ELSE NULL END) as codeshare3_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 4 THEN cro.carrier_id ELSE NULL END) as codeshare4_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 4 THEN cro.flight_number ELSE NULL END) as codeshare4_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 4 THEN cro.relationship ELSE NULL END) as codeshare4_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 5 THEN cro.carrier_id ELSE NULL END) as codeshare5_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 5 THEN cro.flight_number ELSE NULL END) as codeshare5_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 5 THEN cro.relationship ELSE NULL END) as codeshare5_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 6 THEN cro.carrier_id ELSE NULL END) as codeshare6_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 6 THEN cro.flight_number ELSE NULL END) as codeshare6_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 6 THEN cro.relationship ELSE NULL END) as codeshare6_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 7 THEN cro.carrier_id ELSE NULL END) as codeshare7_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 7 THEN cro.flight_number ELSE NULL END) as codeshare7_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 7 THEN cro.relationship ELSE NULL END) as codeshare7_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 8 THEN cro.carrier_id ELSE NULL END) as codeshare8_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 8 THEN cro.flight_number ELSE NULL END) as codeshare8_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 8 THEN cro.relationship ELSE NULL END) as codeshare8_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 9 THEN cro.carrier_id ELSE NULL END) as codeshare9_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 9 THEN cro.flight_number ELSE NULL END) as codeshare9_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 9 THEN cro.relationship ELSE NULL END) as codeshare9_relationship

, LISTAGG(CASE WHEN cro.codeshare_no = 10 THEN cro.carrier_id ELSE NULL END) as codeshare10_carrier_id
, SUM(CASE WHEN cro.codeshare_no = 10 THEN cro.flight_number ELSE NULL END) as codeshare10_flight_number
, LISTAGG(CASE WHEN cro.codeshare_no = 10 THEN cro.relationship ELSE NULL END) as codeshare10_relationship
FROM codeshares_row_order as cro

GROUP BY cro.flight_id
),

diverted_flights as (
SELECT di.flight_id
, di.airport_id
, ai.name
, ai.airport_iata
, ai.airport_icao
, di.airport_utc_offset
, di.actual_runway_departure
, di.actual_runway_arrival
, di.total_ground_time
, di.longest_ground_time
, di.continuation_flight_id
, di.continuation_tail_number
, row_number() over (
partition by di.flight_id
order by
di.actual_runway_departure
) as diverted_flight_no
FROM
diversions as di
LEFT OUTER JOIN airports as ai
ON di.airport_id = ai.airport_id
),

diverted_flights_pivot as (
SELECT flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.airport_id ELSE NULL END) as diversion1_airport_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.name ELSE NULL END) as diversion1_airport_name
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.airport_iata ELSE NULL END) as diversion1_airport_iata
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.airport_icao ELSE NULL END) as diversion1_airport_icao
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.airport_utc_offset ELSE NULL END) as diversion1_airport_utc_offset
, MAX(CASE WHEN df.diverted_flight_no = 1 THEN df.actual_runway_departure ELSE NULL END) as diversion1_actual_runway_departure
, MAX(CASE WHEN df.diverted_flight_no = 1 THEN df.actual_runway_arrival ELSE NULL END) as diversion1_actual_runway_arrival
, SUM(CASE WHEN df.diverted_flight_no = 1 THEN df.total_ground_time ELSE NULL END) as diversion1_total_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 1 THEN df.longest_ground_time ELSE NULL END) as diversion1_longest_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 1 THEN df.continuation_flight_id ELSE NULL END) as diversion1_continuation_flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 1 THEN df.continuation_tail_number ELSE NULL END) as diversion1_continuation_tail_number

, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.airport_id ELSE NULL END) as diversion2_airport_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.name ELSE NULL END) as diversion2_airport_name
, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.airport_iata ELSE NULL END) as diversion2_airport_iata
, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.airport_icao ELSE NULL END) as diversion2_airport_icao
, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.airport_utc_offset ELSE NULL END) as diversion2_airport_utc_offset
, MAX(CASE WHEN df.diverted_flight_no = 2 THEN df.actual_runway_departure ELSE NULL END) as diversion2_actual_runway_departure
, MAX(CASE WHEN df.diverted_flight_no = 2 THEN df.actual_runway_arrival ELSE NULL END) as diversion2_actual_runway_arrival
, SUM(CASE WHEN df.diverted_flight_no = 2 THEN df.total_ground_time ELSE NULL END) as diversion2_total_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 2 THEN df.longest_ground_time ELSE NULL END) as diversion2_longest_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 2 THEN df.continuation_flight_id ELSE NULL END) as diversion2_continuation_flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 2 THEN df.continuation_tail_number ELSE NULL END) as diversion2_continuation_tail_number

, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.airport_id ELSE NULL END) as diversion3_airport_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.name ELSE NULL END) as diversion3_airport_name
, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.airport_iata ELSE NULL END) as diversion3_airport_iata
, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.airport_icao ELSE NULL END) as diversion3_airport_icao
, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.airport_utc_offset ELSE NULL END) as diversion3_airport_utc_offset
, MAX(CASE WHEN df.diverted_flight_no = 3 THEN df.actual_runway_departure ELSE NULL END) as diversion3_actual_runway_departure
, MAX(CASE WHEN df.diverted_flight_no = 3 THEN df.actual_runway_arrival ELSE NULL END) as diversion3_actual_runway_arrival
, SUM(CASE WHEN df.diverted_flight_no = 3 THEN df.total_ground_time ELSE NULL END) as diversion3_total_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 3 THEN df.longest_ground_time ELSE NULL END) as diversion3_longest_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 3 THEN df.continuation_flight_id ELSE NULL END) as diversion3_continuation_flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 3 THEN df.continuation_tail_number ELSE NULL END) as diversion3_continuation_tail_number

, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.airport_id ELSE NULL END) as diversion4_airport_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.name ELSE NULL END) as diversion4_airport_name
, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.airport_iata ELSE NULL END) as diversion4_airport_iata
, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.airport_icao ELSE NULL END) as diversion4_airport_icao
, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.airport_utc_offset ELSE NULL END) as diversion4_airport_utc_offset
, MAX(CASE WHEN df.diverted_flight_no = 4 THEN df.actual_runway_departure ELSE NULL END) as diversion4_actual_runway_departure
, MAX(CASE WHEN df.diverted_flight_no = 4 THEN df.actual_runway_arrival ELSE NULL END) as diversion4_actual_runway_arrival
, SUM(CASE WHEN df.diverted_flight_no = 4 THEN df.total_ground_time ELSE NULL END) as diversion4_total_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 4 THEN df.longest_ground_time ELSE NULL END) as diversion4_longest_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 4 THEN df.continuation_flight_id ELSE NULL END) as diversion4_continuation_flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 4 THEN df.continuation_tail_number ELSE NULL END) as diversion4_continuation_tail_number

, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.airport_id ELSE NULL END) as diversion5_airport_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.name ELSE NULL END) as diversion5_airport_name
, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.airport_iata ELSE NULL END) as diversion5_airport_iata
, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.airport_icao ELSE NULL END) as diversion5_airport_icao
, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.airport_utc_offset ELSE NULL END) as diversion5_airport_utc_offset
, MAX(CASE WHEN df.diverted_flight_no = 5 THEN df.actual_runway_departure ELSE NULL END) as diversion5_actual_runway_departure
, MAX(CASE WHEN df.diverted_flight_no = 5 THEN df.actual_runway_arrival ELSE NULL END) as diversion5_actual_runway_arrival
, SUM(CASE WHEN df.diverted_flight_no = 5 THEN df.total_ground_time ELSE NULL END) as diversion5_total_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 5 THEN df.longest_ground_time ELSE NULL END) as diversion5_longest_ground_time
, SUM(CASE WHEN df.diverted_flight_no = 5 THEN df.continuation_flight_id ELSE NULL END) as diversion5_continuation_flight_id
, LISTAGG(CASE WHEN df.diverted_flight_no = 5 THEN df.continuation_tail_number ELSE NULL END) as diversion5_continuation_tail_number
FROM diverted_flights as df
GROUP BY flight_id
)

SELECT fli.flight_id
, fli.operating_carrier_id
, fli.wet_lease_carrier_id
, fli.fleet_aircraft_id
, fli.service_type_iata
, fli.flight_number
, fli.tail_number
, fli.departure_airport_id
, fli.departure_utc_offset
, fli.departure_terminal
, fli.departure_gate
, fli.published_departure
, fli.planned_departure
, fli.scheduled_gate_departure
, fli.estimated_gate_departure
, fli.first_gate_departure
, fli.actual_gate_departure
, fli.scheduled_runway_departure
, fli.estimated_runway_departure
, fli.actual_runway_departure
, fli.estimated_taxi_out_time
, fli.actual_taxi_out_time
, fli.gate_departure_delay
, fli.arrival_airport_id 
, fli.arrival_utc_offset 
, fli.arrival_terminal 
, fli.arrival_gate 
, fli.baggage_claim 
, fli.published_arrival 
, fli.planned_arrival 
, fli.scheduled_runway_arrival 
, fli.estimated_runway_arrival 
, fli.actual_runway_arrival 
, fli.scheduled_gate_arrival 
, fli.estimated_gate_arrival 
, fli.actual_gate_arrival 
, fli.estimated_taxi_in_time 
, fli.actual_taxi_in_time 
, fli.gate_arrival_delay
, fli.planned_route
, fli.departure_fix
, fli.arrival_fix
, fli.is_diverted
, fli.is_cancelled
, fli.gate_return_total_ground_time
, fli.gate_return_longest_ground_time
, fli.diverted_airport_landings
, fli.diverted_reached_destination
, fli.diverted_actual_elapsed_time
, fli.diverted_arrival_elapsed_time
, fli.diverted_distance_miles
, fli.actual_flight_duration
, fli.scheduled_block_time
, fli.actual_block_time
, fli.block_time_overshoot
, fli.delay_difference_indicator
, fle.aircraft_type
, fle.aircraft_type_series
, fle.aircraft_code_iata
, fle.aircraft_code_icao
, fle.aircraft_serial_number
, fle.operator_code_iata
, fle.operator_code_icao
, fle.total_seat_count
, fle.first_class_seat_count
, fle.business_class_seat_count
, fle.premium_economy_class_seat_count
, fle.economy_class_seat_count
, da.airport_iata as departure_airport_iata
, da.airport_icao as departure_airport_icao
, da.name as departure_airport_name
, aa.airport_iata as arrival_airport_iata
, aa.airport_icao as arrival_airport_icao
, aa.name as arrival_airport_name
, mc.carrier_iata as marketing_carrier_iata
, mc.carrier_icao as marketing_carrier_icao
, mc.name as marketing_carrier_name
, oc.carrier_iata as operating_carrier_iata
, oc.carrier_icao as operating_carrier_icao
, oc.name as operating_carrier_name
, wlc.carrier_iata as wet_lease_carrier_iata
, wlc.carrier_icao as wet_lease_carrier_icao
, wlc.name as wet_lease_carrier_name
, csp.codeshare1_carrier_id
, csp.codeshare1_flight_number
, csp.codeshare1_relationship
, csp.codeshare2_carrier_id
, csp.codeshare2_flight_number
, csp.codeshare2_relationship
, csp.codeshare3_carrier_id
, csp.codeshare3_flight_number
, csp.codeshare3_relationship
, csp.codeshare4_carrier_id
, csp.codeshare4_flight_number
, csp.codeshare4_relationship
, csp.codeshare5_carrier_id
, csp.codeshare5_flight_number
, csp.codeshare5_relationship
, csp.codeshare6_carrier_id
, csp.codeshare6_flight_number
, csp.codeshare6_relationship
, csp.codeshare7_carrier_id
, csp.codeshare7_flight_number
, csp.codeshare7_relationship
, csp.codeshare8_carrier_id
, csp.codeshare8_flight_number
, csp.codeshare8_relationship
, csp.codeshare9_carrier_id
, csp.codeshare9_flight_number
, csp.codeshare9_relationship
, csp.codeshare10_carrier_id
, csp.codeshare10_flight_number
, csp.codeshare10_relationship
, dfp.diversion1_airport_id
, dfp.diversion1_airport_name
, dfp.diversion1_airport_iata
, dfp.diversion1_airport_icao
, dfp.diversion1_airport_utc_offset
, dfp.diversion1_actual_runway_departure
, dfp.diversion1_actual_runway_arrival
, dfp.diversion1_total_ground_time
, dfp.diversion1_longest_ground_time
, dfp.diversion1_continuation_flight_id
, dfp.diversion1_continuation_tail_number
, dfp.diversion2_airport_id
, dfp.diversion2_airport_name
, dfp.diversion2_airport_iata
, dfp.diversion2_airport_icao
, dfp.diversion2_airport_utc_offset
, dfp.diversion2_actual_runway_departure
, dfp.diversion2_actual_runway_arrival
, dfp.diversion2_total_ground_time
, dfp.diversion2_longest_ground_time
, dfp.diversion2_continuation_flight_id
, dfp.diversion2_continuation_tail_number
, dfp.diversion3_airport_id
, dfp.diversion3_airport_name
, dfp.diversion3_airport_iata
, dfp.diversion3_airport_icao
, dfp.diversion3_airport_utc_offset
, dfp.diversion3_actual_runway_departure
, dfp.diversion3_actual_runway_arrival
, dfp.diversion3_total_ground_time
, dfp.diversion3_longest_ground_time
, dfp.diversion3_continuation_flight_id
, dfp.diversion3_continuation_tail_number
, dfp.diversion4_airport_id
, dfp.diversion4_airport_name
, dfp.diversion4_airport_iata
, dfp.diversion4_airport_icao
, dfp.diversion4_airport_utc_offset
, dfp.diversion4_actual_runway_departure
, dfp.diversion4_actual_runway_arrival
, dfp.diversion4_total_ground_time
, dfp.diversion4_longest_ground_time
, dfp.diversion4_continuation_flight_id 
, dfp.diversion4_continuation_tail_number
, dfp.diversion5_airport_id
, dfp.diversion5_airport_name
, dfp.diversion5_airport_iata
, dfp.diversion5_airport_icao
, dfp.diversion5_airport_utc_offset
, dfp.diversion5_actual_runway_departure
, dfp.diversion5_actual_runway_arrival
, dfp.diversion5_total_ground_time
, dfp.diversion5_longest_ground_time
, dfp.diversion5_continuation_flight_id 
, dfp.diversion5_continuation_tail_number

FROM flights as fli
LEFT OUTER JOIN fleets as fle
ON fli.fleet_aircraft_id = fle.aircraft_id
INNER JOIN airports as da
ON fli.departure_airport_id = da.airport_id
INNER JOIN airports as aa
ON fli.arrival_airport_id = aa.airport_id
LEFT OUTER JOIN carriers as mc
ON fli.operating_carrier_id = mc.carrier_id
LEFT OUTER JOIN carriers as oc
ON fli.operating_carrier_id = oc.carrier_id
LEFT OUTER JOIN carriers as wlc
ON fli.wet_lease_carrier_id = wlc.carrier_id
LEFT OUTER JOIN codeshares_pivot as csp
ON fli.flight_id = csp.flight_id
LEFT OUTER JOIN diverted_flights_pivot as dfp
ON fli.flight_id = dfp.flight_id AND fli.is_diverted = 1;

If you have any feedback or ideas about Cirium Sky Data Warehouse then we would love to hear from you via the Cirium Help Desk.