Flight Data — Hourly Congestion Pattern

Flight Data benchmark · all Flight Data queries

Flight volume and delay by scheduled departure hour of day

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 hour_of_day,
  COUNT(*) AS total_flights,
  ROUND(AVG(CASE WHEN f.cancelled = 0 THEN f.dep_delay END), 2) AS avg_dep_delay,
  ROUND(AVG(CASE WHEN f.cancelled = 0 THEN f.arr_delay END), 2) AS avg_arr_delay,
  SUM(CASE WHEN f.dep_delay > 60 THEN 1 ELSE 0 END) AS severely_delayed,
  ROUND(
    100.0 * SUM(CASE WHEN f.cancelled = 0 AND f.arr_delay <= 15 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN f.cancelled = 0 THEN 1 ELSE 0 END), 0),
    2
  ) AS ontime_pct
FROM flights AS f
WHERE
  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
  hour_of_day

Representative DataFrame

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

def time_of_day_expression_impl(ctx: DataFrameContext) -> Any:
    flights, col, lit = _date_window(ctx, lambda col, _lit: col("crs_dep_time").is_not_null())

    return (
        flights.with_columns(
            (col("crs_dep_time") / lit(100)).floor().cast(int).alias("hour_of_day"),
            (col("dep_delay") > lit(60)).cast(int).alias("_severe"),
            ((col("cancelled") == lit(0)) & (col("arr_delay") <= lit(15))).cast(int).alias("_ontime"),
            (col("cancelled") == lit(0)).cast(int).alias("_operated"),
            ctx.when(col("cancelled") == lit(0)).then(col("dep_delay")).otherwise(lit(None)).alias("_dep_nc"),
            ctx.when(col("cancelled") == lit(0)).then(col("arr_delay")).otherwise(lit(None)).alias("_arr_nc"),
        )
        .group_by("hour_of_day")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_dep_nc").mean().alias("_avg_dep"),
            col("_arr_nc").mean().alias("_avg_arr"),
            col("_severe").sum().alias("severely_delayed"),
            col("_ontime").sum().alias("_ontime_sum"),
            col("_operated").sum().alias("_non_cancelled"),
        )
        .with_columns(
            ctx.when(col("_non_cancelled") > lit(0))
            .then(col("_non_cancelled"))
            .otherwise(lit(None))
            .alias("_non_cancelled_nz"),
        )
        .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("_ontime_sum") / col("_non_cancelled_nz"), 2).alias("ontime_pct"),
        )
        .select(
            "hour_of_day",
            "total_flights",
            "avg_dep_delay",
            "avg_arr_delay",
            "severely_delayed",
            "ontime_pct",
        )
        .sort("hour_of_day")
    )

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()['time-of-day'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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