Flight Data — Delay Recovery Analysis

Flight Data benchmark · all Flight Data queries

How much of departure delay is recovered in flight (dep_delay - arr_delay)

Representative SQL

Rendered in the benchmark’s native SQL with default parameters (no dialect translation available).

SELECT
  CASE
    WHEN f.dep_delay <= 0
    THEN 'No departure delay'
    WHEN f.dep_delay <= 15
    THEN '1-15 min departure delay'
    WHEN f.dep_delay <= 30
    THEN '16-30 min departure delay'
    WHEN f.dep_delay <= 60
    THEN '31-60 min departure delay'
    ELSE '60+ min departure delay'
  END AS delay_bucket,
  COUNT(*) AS flight_count,
  ROUND(AVG(f.dep_delay), 2) AS avg_dep_delay,
  ROUND(AVG(f.arr_delay), 2) AS avg_arr_delay,
  ROUND(AVG(f.dep_delay - f.arr_delay), 2) AS avg_minutes_recovered,
  ROUND(100.0 * SUM(CASE WHEN f.arr_delay < f.dep_delay THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_recovered
FROM flights AS f
WHERE
  f.cancelled = 0
  AND NOT f.dep_delay IS NULL
  AND NOT f.arr_delay IS NULL
  AND f.flight_date >= '2021-08-01'
  AND f.flight_date < '2025-01-01'
GROUP BY
  CASE
    WHEN f.dep_delay <= 0
    THEN 'No departure delay'
    WHEN f.dep_delay <= 15
    THEN '1-15 min departure delay'
    WHEN f.dep_delay <= 30
    THEN '16-30 min departure delay'
    WHEN f.dep_delay <= 60
    THEN '31-60 min departure delay'
    ELSE '60+ min departure delay'
  END
ORDER BY
  avg_dep_delay

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def recovery_time_expression_impl(ctx: DataFrameContext) -> Any:
    flights, col, lit = _date_window(
        ctx,
        lambda col, lit: (
            col("cancelled") == lit(0),
            col("dep_delay").is_not_null(),
            col("arr_delay").is_not_null(),
        ),
    )

    return (
        flights.with_columns(
            (col("dep_delay") - col("arr_delay")).alias("minutes_recovered"),
            (col("arr_delay") < col("dep_delay")).cast(int).alias("recovered_flag"),
            ctx.when(col("dep_delay") <= lit(0))
            .then(lit("No departure delay"))
            .when(col("dep_delay") <= lit(15))
            .then(lit("1-15 min departure delay"))
            .when(col("dep_delay") <= lit(30))
            .then(lit("16-30 min departure delay"))
            .when(col("dep_delay") <= lit(60))
            .then(lit("31-60 min departure delay"))
            .otherwise(lit("60+ min departure delay"))
            .alias("delay_bucket"),
        )
        .group_by("delay_bucket")
        .agg(
            col("flight_id").count().alias("flight_count"),
            col("dep_delay").mean().alias("_avg_dep"),
            col("arr_delay").mean().alias("_avg_arr"),
            col("minutes_recovered").mean().alias("_avg_recovered"),
            col("recovered_flag").sum().alias("_recovered_sum"),
        )
        .with_columns(
            _round_half_away(ctx, col("_avg_dep"), 2).alias("avg_dep_delay"),
            _round_half_away(ctx, col("_avg_arr"), 2).alias("avg_arr_delay"),
            _round_half_away(ctx, col("_avg_recovered"), 2).alias("avg_minutes_recovered"),
            _round_half_away(ctx, lit(100.0) * col("_recovered_sum") / col("flight_count"), 2).alias("pct_recovered"),
        )
        .select(
            "delay_bucket",
            "flight_count",
            "avg_dep_delay",
            "avg_arr_delay",
            "avg_minutes_recovered",
            "pct_recovered",
        )
        .sort("avg_dep_delay")
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import FlightData

bench = FlightData(scale_factor=1.0)            # your scale factor
print(bench.get_queries()['recovery-time'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('flightdata', 'recovery-time', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/flightdata/queries.py