Flight Data — Distance vs Delay Correlation

Flight Data benchmark · all Flight Data queries

Average delay grouped by flight distance buckets

Representative SQL

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

SELECT
  CASE
    WHEN f.distance < 250
    THEN 'Short-haul (<250 mi)'
    WHEN f.distance < 500
    THEN 'Medium-haul (250-499 mi)'
    WHEN f.distance < 1000
    THEN 'Long-haul (500-999 mi)'
    WHEN f.distance < 2000
    THEN 'Cross-country (1000-1999 mi)'
    ELSE 'Ultra-long (2000+ mi)'
  END AS distance_bucket,
  COUNT(*) AS total_flights,
  ROUND(AVG(f.distance), 0) AS avg_distance_miles,
  ROUND(AVG(f.dep_delay), 2) AS avg_dep_delay,
  ROUND(AVG(f.arr_delay), 2) AS avg_arr_delay,
  ROUND(100.0 * SUM(CASE WHEN f.arr_delay <= 15 THEN 1 ELSE 0 END) / COUNT(*), 2) AS ontime_pct
FROM flights AS f
WHERE
  f.cancelled = 0
  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.distance < 250
    THEN 'Short-haul (<250 mi)'
    WHEN f.distance < 500
    THEN 'Medium-haul (250-499 mi)'
    WHEN f.distance < 1000
    THEN 'Long-haul (500-999 mi)'
    WHEN f.distance < 2000
    THEN 'Cross-country (1000-1999 mi)'
    ELSE 'Ultra-long (2000+ mi)'
  END
ORDER BY
  avg_distance_miles

Representative DataFrame

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

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

    return (
        flights.with_columns(
            ctx.when(col("distance") < lit(250))
            .then(lit("Short-haul (<250 mi)"))
            .when(col("distance") < lit(500))
            .then(lit("Medium-haul (250-499 mi)"))
            .when(col("distance") < lit(1000))
            .then(lit("Long-haul (500-999 mi)"))
            .when(col("distance") < lit(2000))
            .then(lit("Cross-country (1000-1999 mi)"))
            .otherwise(lit("Ultra-long (2000+ mi)"))
            .alias("distance_bucket"),
        )
        .with_columns((col("arr_delay") <= lit(15)).cast(int).alias("_ontime"))
        .group_by("distance_bucket")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("distance").mean().alias("_avg_dist"),
            col("dep_delay").mean().alias("_avg_dep"),
            col("arr_delay").mean().alias("_avg_arr"),
            col("_ontime").sum().alias("_ontime_sum"),
        )
        .with_columns(
            _round_half_away(ctx, col("_avg_dist"), 0).alias("avg_distance_miles"),
            _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, lit(100.0) * col("_ontime_sum") / col("total_flights"), 2).alias("ontime_pct"),
        )
        .select(
            "distance_bucket",
            "total_flights",
            "avg_distance_miles",
            "avg_dep_delay",
            "avg_arr_delay",
            "ontime_pct",
        )
        .sort("avg_distance_miles")
    )

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()['distance-delay'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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