Flight Data — Delay Pattern by Departure Hour

Flight Data benchmark · all Flight Data queries

Average delay minutes by scheduled departure hour

Representative SQL

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

SELECT
  CAST(FLOOR(f.crs_dep_time / 100) AS INT) AS dep_hour,
  COUNT(*) AS total_flights,
  ROUND(AVG(f.dep_delay), 2) AS avg_dep_delay,
  ROUND(AVG(f.arr_delay), 2) AS avg_arr_delay,
  SUM(CASE WHEN f.dep_delay > 15 THEN 1 ELSE 0 END) AS delayed_count,
  ROUND(100.0 * SUM(CASE WHEN f.dep_delay > 15 THEN 1 ELSE 0 END) / COUNT(*), 2) AS delay_rate_pct
FROM flights AS f
WHERE
  f.cancelled = 0
  AND NOT f.dep_delay IS NULL
  AND NOT f.crs_dep_time IS NULL
  AND f.flight_date >= '2021-08-01'
  AND f.flight_date < '2025-01-01'
GROUP BY
  CAST(FLOOR(f.crs_dep_time / 100) AS INT)
ORDER BY
  dep_hour

Representative DataFrame

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

def delay_by_hour_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("crs_dep_time").is_not_null(),
        ),
    )

    return (
        flights.with_columns(
            (col("crs_dep_time") / lit(100)).floor().cast(int).alias("dep_hour"),
            (col("dep_delay") > lit(15)).cast(int).alias("_delayed"),
        )
        .group_by("dep_hour")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("dep_delay").mean().alias("_avg_dep"),
            col("arr_delay").mean().alias("_avg_arr"),
            col("_delayed").sum().alias("delayed_count"),
        )
        .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, lit(100.0) * col("delayed_count") / col("total_flights"), 2).alias("delay_rate_pct"),
        )
        .select("dep_hour", "total_flights", "avg_dep_delay", "avg_arr_delay", "delayed_count", "delay_rate_pct")
        .sort("dep_hour")
    )

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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