How to read it: each bubble is a UK airport in the easyJet network. Bubble size = annual flight volume; colour = OTP (red < 65%, amber 65–80%, green ≥ 80%). Hover any bubble for exact figures. Larger / redder bubbles are the ones consuming the most operational attention.

How to read it: rows are UK airports, columns are years, cell colour is average arrival delay (green = low, red = high). Look for hot rows (consistently bad airports) vs hot columns (bad years across the board, e.g. 2022).

What it shows: easyJet's OTP at each UK base, sorted ascending. The 80% target line makes the gap visible at a glance.

What it shows: the year-over-year shift in OTP at each airport. Green bars are improving airports; red are deteriorating. Magnitude indicates how rapid the change is.

What it shows: annual OTP for each major UK carrier. easyJet's line is bold. Finding: the 2022 collapse was sector-wide; recovery has been incremental. Ryanair recovers fastest, suggesting LCC operating discipline pays off post-shock.

How to read it: each bar is a "train on history → predict next year → score" cycle. Bar height = MAE (lower is better); the line shows what fraction of actuals fell inside the model's 80% prediction interval. Finding: the 2022 fold is bad (no model could predict the post-COVID demand surge); 2023 and 2024 folds settle to ~10 pp MAE with well-calibrated intervals — meaningful for triage despite annual-only data.

What it shows: volume-weighted OTP across every UK airport, all carriers pooled — isolates the airport from any single airline's mix. Finding: Gatwick (60.2%) is the worst-performing UK airport in 2024 — Belfast City achieves 79% on the same dataset.

How to read it: grey bar = the airline's OTP at non-Gatwick UK airports; coloured bar = same airline's OTP at Gatwick. The gap on each pair is their Gatwick penalty. Every airline gives up ~8–15 pp at Gatwick — Ryanair is the outlier at only 2.5 pp, suggesting operational discipline can soften the airport effect.

What it shows: easyJet's Gatwick routes split into Mediterranean/leisure (24 destinations) vs everything else. Bars are OTP; diamonds are average delay. Finding (with 95% CI): the leisure segment trails the other segment by 9.7 pp [95% CI: 5.5–15.3] — bootstrapped over 22 Med routes vs 96 Other routes. The interval is comfortably above zero, so this is a statistically robust airline-controllable lever. See Prescription P1 below.

These queries run offline, not here. DuckDB is used in the build pipeline to transform the CAA data into the pre-computed JSON this dashboard loads. The published site is static — it holds no database and executes no SQL at runtime. The schema and queries below are included to show how the analysis was actually done.

Fact table holds annual route-airline grain; three dimension tables conform on surrogate keys. All weighted aggregates use flown_flights as the weight.

-- Fact
fact_punctuality (
    fact_id PK,
    airline_id FK, origin_airport_id FK, dest_airport_id FK, date_id FK,
    scheduled_flights, flown_flights, cancelled_flights,
    pct_ontime_15, avg_delay_mins,
    pct_15_30, pct_31_60, pct_61_120, pct_121_180, pct_181_360, pct_361_plus
)

-- Dimensions
dim_airline (airline_id PK, airline_name, iata, icao, country, type)
dim_airport (airport_id PK, name, iata, icao, lat, lon, country, city)
dim_date    (date_id PK, year, quarter, month, period_label)

1 · Rank airlines within each airport (window function)

SELECT
    a.airport_name,
    al.airline_name,
    f.pct_ontime_15,
    RANK() OVER (PARTITION BY a.airport_id ORDER BY f.pct_ontime_15 DESC) AS otp_rank,
    f.pct_ontime_15 - AVG(f.pct_ontime_15) OVER (PARTITION BY a.airport_id) AS vs_airport_avg
FROM fact_punctuality f
JOIN dim_airline al ON f.airline_id = al.airline_id
JOIN dim_airport a  ON f.origin_airport_id = a.airport_id
WHERE f.year = 2024 AND f.flown_flights >= 100
ORDER BY a.airport_name, otp_rank;

2 · Weighted YoY OTP with rolling 3-year average (CTE + window)

WITH annual AS (
    SELECT
        al.airline_name,
        d.year,
        SUM(f.flown_flights * f.pct_ontime_15) / NULLIF(SUM(f.flown_flights), 0) AS weighted_otp,
        SUM(f.flown_flights) AS flights
    FROM fact_punctuality f
    JOIN dim_airline al ON f.airline_id = al.airline_id
    JOIN dim_date d     ON f.date_id    = d.date_id
    GROUP BY al.airline_name, d.year
)
SELECT
    airline_name, year, weighted_otp,
    AVG(weighted_otp) OVER (PARTITION BY airline_name ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3yr,
    weighted_otp - LAG(weighted_otp) OVER (PARTITION BY airline_name ORDER BY year)                          AS yoy_change
FROM annual WHERE flights >= 500
ORDER BY airline_name, year;

Bootstrap confidence intervals on the Case Study findings

The Case Study page reports several point estimates as differences of volume-weighted means — most notably the Gatwick penalty per airline (carrier's Gatwick OTP minus their non-Gatwick OTP) and the Med-vs-Other segment gap for easyJet at Gatwick. Closed-form confidence intervals for weighted-mean differences are awkward (Welch's t with weights isn't standard), so we use a nonparametric percentile bootstrap:

1. The resampling unit is one route — i.e. an (origin, destination, year) row from the fact table, restricted to routes with ≥20 flights to drop noise.
2. For each comparison we resample with replacement from both pools independently B = 2,000 times.
3. Each resample recomputes the volume-weighted OTP for each pool and takes the difference.
4. The 95% CI is the 2.5th and 97.5th percentiles of the resulting bootstrap distribution.
5. Fixed random seed (seed=42) so the figures are reproducible across rebuilds.

Honest reading. A CI that excludes zero means the gap is unlikely to be sampling noise. A CI that includes zero (e.g. Ryanair's [−4.5, +4.9] on the Gatwick penalty) tells us the apparent effect could be explained by which routes happen to be in our sample — we shouldn't claim it as a real finding. The bootstrap is the simplest defensible way to publish these gaps without overclaiming.

Predictive Delay Risk Classifier