Block Performance Over Time

SELECT 
date_trunc('month', fli.published_departure) as date,  
-- Modify the datepart string to adjust the interval of the date series.
da.name as departure_airport_name, 
da.airport_id as departure_airport_id, 
da.state_code as departure_state, 
da.country_code as departure_country, 
aa.name as arrival_airport_name, 
aa.airport_id as arrival_airport_id, 
aa.state_code as arrival_state, 
aa.country_code as arrival_country, 
ca.name as operator_name, 
ca.carrier_id as operator_id, 
fle.aircraft_type_series, 
COUNT(*) as total_flights, 
COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as completed_flights, 
AVG(fli.scheduled_block_time) as avg_scheduled_block, 
AVG(fli.actual_block_time) as avg_actual_block, 
AVG(fli.block_time_overshoot) as avg_block_overshoot, 
SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1 ELSE 0 END) as not_completed_flights, 
SUM(CASE WHEN fli.block_time_overshoot <= 0 THEN 1 ELSE 0 END) as b0s, 
AVG(fli.gate_departure_delay) as average_departure_delay, 
SUM(CASE WHEN fli.gate_departure_delay <= 0 THEN 1 ELSE 0 END) AS d0s, 
SUM(CASE WHEN fli.gate_departure_delay <= 14 THEN 1 ELSE 0 END) AS d14s, 
SUM(CASE WHEN fli.gate_departure_delay <= 15 THEN 1 ELSE 0 END) AS d15s, 
AVG(fli.gate_arrival_delay) as average_departure_delay, 
SUM(CASE WHEN fli.gate_arrival_delay <= 0 THEN 1 ELSE 0 END) AS a0s, 
SUM(CASE WHEN fli.gate_arrival_delay <= 14 THEN 1 ELSE 0 END) AS a14s, 
SUM(CASE WHEN fli.gate_arrival_delay <= 15 THEN 1 ELSE 0 END) AS a15s

from flights as fli

INNER JOIN airports da ON fli.departure_airport_id = da.airport_id
INNER JOIN airports aa ON fli.arrival_airport_id = aa.airport_id
INNER JOIN fleets fle ON fli.fleet_aircraft_id = fle.aircraft_id
INNER JOIN carriers ca ON fli.operating_carrier_id = ca.carrier_id

GROUP BY date
, departure_airport_name
, ca.name
, da.airport_id
, da.state_code
, da.country_code
, aa.name
, aa.airport_id
, aa.state_code
, aa.country_code
, ca.name
, ca.carrier_id
, fle.aircraft_type_series;

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