Performance Benchmarks (Grouped)

This query will calculate the performance metrics of given grouping values over a specified date range.

To add grouping values either uncomment below in both the select statement and the grouping statement or add from the flights (fli), fleets (fle), arrival or departure airports (aa & da) or operator information (ca).

Ensure these are added to both the select and group statements.

Useful example filters, including distinct date ranges are included in the where section.

SELECT
-- || ----- Flight Fields ----- ||
-- , fli.flight_number

-- || ----- Departure Airport Fields ----- ||
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
-- , fli.departure_terminal
-- , fli.departure_gate

-- || ----- Arrival Airport Fields ----- ||
-- , 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
-- , fli.arrival_terminal
-- , fli.arrival_gate

-- || ----- Carrier Fields ----- ||
-- , ca.name as operator_name
-- , ca.carrier_id as operator_id

-- || ----- Fleet Fields ----- ||
, fle.aircraft_family
-- , fle.aircraft_type_series
-- , fle.engine_master_series
-- , fle.aircraft_serial_number

-- || ----- Friendly Fields ----- ||
-- , da.name || ' -> ' || aa.name as route
-- , '(' || ca.carrier_id || ') ' || ca.name as operating_airline

, 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
, SUM(fle.total_seat_count) as total_seats
, SUM(CASE WHEN fli.is_cancelled = 1 THEN 1 ELSE 0 END) as cancelled_flights

, SUM(CASE WHEN (fli.actual_gate_departure IS NOT NULL OR fli.actual_runway_departure IS NOT NULL) AND (fli.actual_gate_arrival IS NOT NULL AND fli.actual_runway_arrival IS NOT NULL) THEN 1.0 ELSE 0.0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as CF_perc

, SUM(CASE WHEN fli.actual_gate_departure IS NOT NULL OR fli.actual_runway_departure IS NOT NULL THEN 1.0 ELSE 0.0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as dep_CF_perc

, SUM(CASE WHEN fli.actual_gate_arrival IS NOT NULL AND fli.actual_runway_arrival IS NOT NULL THEN 1.0 ELSE 0.0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 THEN 1.0 ELSE 0.0 END)), 0) as arr_CF_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 0 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d0_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d5_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d14_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d15_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d30_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d60_perc

, SUM(CASE WHEN fli.gate_departure_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_departure IS NULL AND fli.actual_runway_departure IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as d120_perc

, AVG(fli.gate_departure_delay) as avg_dep_delay

, SUM(CASE WHEN fli.gate_arrival_delay <= 0 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a0_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 5 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a5_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 14 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a14_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 15 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a15_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 30 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a30_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 60 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a60_perc

, SUM(CASE WHEN fli.gate_arrival_delay <= 120 AND (fli.is_diverted = 0 AND fli.is_cancelled = 0) THEN 1 ELSE 0 END) 
/ nullif((COUNT(*) - SUM(CASE WHEN fli.is_diverted = 1 OR fli.is_cancelled = 1 
OR (fli.actual_gate_arrival IS NULL AND fli.actual_runway_arrival IS NULL) THEN 1.0 ELSE 0.0 END)), 0) as a120_perc

, AVG(fli.gate_arrival_delay) as avg_arr_delay
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

WHERE 
-- fli.scheduled_gate_departure BETWEEN to_date('05/01/2021', 'DD/MM/YYYY') AND to_date('10/02/2021', 'DD/MM/YYYY') -- Select flights in a given date range
-- AND 
fli.scheduled_gate_departure >= dateadd('day', -20, getdate()) -- Select flight with dates in the last X months, years, days etc
-- AND
-- ca.carrier_id IN ('DL', 'AA') -- Filter by carriers
-- AND
-- da.airport_id IN ('PDX') -- Filter by departure airports
-- AND
-- aa.airport_id IN ('LAX') -- Filter by arrival airports
-- AND
-- da.country_code = aa.country_code -- Domestic Flights Only

GROUP BY 
-- || ----- Flight Fields ----- ||
-- , fli.flight_number

-- || ----- Departure Airport Fields ----- ||
da.name
, da.airport_id
, da.state_code
, da.country_code
-- , fli.departure_terminal
-- , fli.departure_gate

-- || ----- Arrival Airport Fields ----- ||
-- , 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
-- , fli.arrival_terminal
-- , fli.arrival_gate

-- || ----- Carrier Fields ----- ||
-- , ca.name as operator_name
-- , ca.carrier_id as operator_id

-- || ----- Fleet Fields ----- ||
, fle.aircraft_family
-- , fle.aircraft_type_series
-- , fle.engine_master_series
-- , fle.aircraft_serial_number

-- || ----- Friendly Fields ----- ||
-- , da.name || ' -> ' || aa.name
-- , '(' || ca.carrier_id || ') ' || ca.name
;

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.