Yearly Carrier Leadership — Completed Flights

Who led US domestic aviation by completed flights, year by year · BTS On-Time Performance 1987–2025

Key Metrics

KPI cards will appear here after run

Carrier Rank Over Time

Bump chart loads after run

Leadership Transitions

Transition table loads after run

Analytical Summary

Narrative loads after run

Query Controls & Data Provenance

Year range: –

Primary query — edit and re-run at any time; use __START_YEAR__ / __END_YEAR__ for range substitution

-- Yearly carrier leadership by completed flights
-- One row per year: leader, runner-up, share/gap metrics, and leadership transition flag
-- __START_YEAR__ and __END_YEAR__ are substituted from the year-range inputs above
WITH
    -- Step 1: completed flights per carrier per year (within selected range)
    yearly_carrier AS (
        SELECT
            Year,
            Reporting_Airline AS carrier,
            countIf(Cancelled = 0) AS flights
        FROM ontime.fact_ontime
        WHERE Year BETWEEN __START_YEAR__ AND __END_YEAR__
        GROUP BY Year, carrier
    ),
    -- Step 2: rank carriers within each year and compute year total
    with_rank AS (
        SELECT
            Year,
            carrier,
            flights,
            SUM(flights) OVER (PARTITION BY Year) AS year_total,
            rank() OVER (PARTITION BY Year ORDER BY flights DESC) AS rnk
        FROM yearly_carrier
    ),
    -- Step 3: pivot to one row per year with leader (rank 1) and runner-up (rank 2)
    pivoted AS (
        SELECT
            Year,
            maxIf(carrier, rnk = 1)            AS leader_carrier,
            maxIf(toInt64(flights), rnk = 1)   AS leader_flights,
            maxIf(carrier, rnk = 2)            AS runner_carrier,
            maxIf(toInt64(flights), rnk = 2)   AS runner_flights,
            max(year_total)                     AS year_total
        FROM with_rank
        WHERE rnk <= 2
        GROUP BY Year
    )
-- Step 4: compute share, gap, and leadership transition
SELECT
    Year,
    leader_carrier,
    leader_flights,
    round(leader_flights * 100.0 / year_total, 2)                    AS leader_share_pct,
    runner_carrier,
    runner_flights,
    round(runner_flights * 100.0 / year_total, 2)                    AS runner_share_pct,
    leader_flights - runner_flights                                    AS gap_flights,
    round((leader_flights - runner_flights) * 100.0 / year_total, 2) AS gap_share_pct_pts,
    lagInFrame(leader_carrier, 1, leader_carrier)
        OVER (ORDER BY Year ASC)                                      AS prev_leader,
    if(
        lagInFrame(leader_carrier, 1, leader_carrier)
            OVER (ORDER BY Year ASC) != leader_carrier,
        1, 0
    )                                                                 AS leadership_changed
FROM pivoted
ORDER BY Year

Supporting query (bump & share charts) — ranks top 7 carriers per year; runs automatically with primary

-- Carrier rank lookup for bump & share charts (top 7 by total flights)
-- __START_YEAR__ and __END_YEAR__ substituted from year-range inputs
WITH
    yearly_carrier AS (
        SELECT
            Year,
            Reporting_Airline AS carrier,
            countIf(Cancelled = 0) AS flights
        FROM ontime.fact_ontime
        WHERE Year BETWEEN __START_YEAR__ AND __END_YEAR__
        GROUP BY Year, carrier
    ),
    year_totals AS (
        SELECT Year, sum(flights) AS year_total
        FROM yearly_carrier
        GROUP BY Year
    ),
    top_carriers AS (
        SELECT carrier
        FROM yearly_carrier
        GROUP BY carrier
        ORDER BY sum(flights) DESC
        LIMIT 7
    ),
    all_ranked AS (
        SELECT
            y.Year,
            y.carrier,
            y.flights,
            t.year_total,
            rank() OVER (PARTITION BY y.Year ORDER BY y.flights DESC) AS rnk
        FROM yearly_carrier y
        JOIN year_totals t ON t.Year = y.Year
    )
SELECT
    Year,
    carrier,
    flights,
    round(flights * 100.0 / year_total, 2) AS share_pct,
    toUInt16(rnk)                           AS rnk
FROM all_ranked
WHERE carrier IN (SELECT carrier FROM top_carriers)
ORDER BY Year, rnk

Query ledger

	Label	Role	Range	Status	Rows
No queries run yet.

Sign in to explore