Flight Data — Holiday Period Delay Impact

Flight Data benchmark · all Flight Data queries

Compare performance during major holiday periods vs normal days

Representative SQL

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

SELECT
  CASE
    WHEN (
      f.month = 11 AND f.day_of_month BETWEEN 21 AND 27
    )
    THEN 'Thanksgiving Week'
    WHEN (
      f.month = 12 AND f.day_of_month BETWEEN 23 AND 27
    )
    THEN 'Christmas'
    WHEN (
      f.month = 1 AND f.day_of_month BETWEEN 1 AND 2
    )
    OR (
      f.month = 12 AND f.day_of_month = 31
    )
    THEN 'New Year'
    WHEN (
      f.month = 7 AND f.day_of_month BETWEEN 3 AND 5
    )
    THEN 'July 4th'
    WHEN (
      f.month = 5 AND f.day_of_week = 1 AND f.day_of_month BETWEEN 25 AND 31
    )
    THEN 'Memorial Day'
    WHEN (
      f.month = 9 AND f.day_of_week = 1 AND f.day_of_month BETWEEN 1 AND 7
    )
    THEN 'Labor Day'
    ELSE 'Regular Day'
  END AS period,
  COUNT(*) AS total_flights,
  ROUND(AVG(CASE WHEN f.cancelled = 0 THEN f.arr_delay END), 2) AS avg_arr_delay,
  ROUND(100.0 * SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate_pct,
  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
  f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  CASE
    WHEN (
      f.month = 11 AND f.day_of_month BETWEEN 21 AND 27
    )
    THEN 'Thanksgiving Week'
    WHEN (
      f.month = 12 AND f.day_of_month BETWEEN 23 AND 27
    )
    THEN 'Christmas'
    WHEN (
      f.month = 1 AND f.day_of_month BETWEEN 1 AND 2
    )
    OR (
      f.month = 12 AND f.day_of_month = 31
    )
    THEN 'New Year'
    WHEN (
      f.month = 7 AND f.day_of_month BETWEEN 3 AND 5
    )
    THEN 'July 4th'
    WHEN (
      f.month = 5 AND f.day_of_week = 1 AND f.day_of_month BETWEEN 25 AND 31
    )
    THEN 'Memorial Day'
    WHEN (
      f.month = 9 AND f.day_of_week = 1 AND f.day_of_month BETWEEN 1 AND 7
    )
    THEN 'Labor Day'
    ELSE 'Regular Day'
  END
ORDER BY
  avg_arr_delay DESC

Representative DataFrame

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

def holiday_impact_expression_impl(ctx: DataFrameContext) -> Any:
    flights, col, lit = _date_window(ctx)

    thanksgiving = (col("month") == lit(11)) & (col("day_of_month") >= lit(21)) & (col("day_of_month") <= lit(27))
    christmas = (col("month") == lit(12)) & (col("day_of_month") >= lit(23)) & (col("day_of_month") <= lit(27))
    new_year = ((col("month") == lit(1)) & (col("day_of_month") <= lit(2))) | (
        (col("month") == lit(12)) & (col("day_of_month") == lit(31))
    )
    july4 = (col("month") == lit(7)) & (col("day_of_month") >= lit(3)) & (col("day_of_month") <= lit(5))
    memorial = (
        (col("month") == lit(5))
        & (col("day_of_week") == lit(1))
        & (col("day_of_month") >= lit(25))
        & (col("day_of_month") <= lit(31))
    )
    labor = (
        (col("month") == lit(9))
        & (col("day_of_week") == lit(1))
        & (col("day_of_month") >= lit(1))
        & (col("day_of_month") <= lit(7))
    )

    return (
        flights.with_columns(
            ctx.when(thanksgiving)
            .then(lit("Thanksgiving Week"))
            .when(christmas)
            .then(lit("Christmas"))
            .when(new_year)
            .then(lit("New Year"))
            .when(july4)
            .then(lit("July 4th"))
            .when(memorial)
            .then(lit("Memorial Day"))
            .when(labor)
            .then(lit("Labor Day"))
            .otherwise(lit("Regular Day"))
            .alias("period"),
        )
        .with_columns(
            (col("cancelled") == lit(1)).cast(int).alias("_cancelled"),
            ((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("arr_delay")).otherwise(lit(None)).alias("_arr_nc"),
        )
        .group_by("period")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_arr_nc").mean().alias("_avg_arr"),
            col("_cancelled").sum().alias("_cancelled_sum"),
            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_arr"), 2).alias("avg_arr_delay"),
            _round_half_away(ctx, lit(100.0) * col("_cancelled_sum") / col("total_flights"), 2).alias(
                "cancellation_rate_pct"
            ),
            _round_half_away(ctx, lit(100.0) * col("_ontime_sum") / col("_non_cancelled_nz"), 2).alias("ontime_pct"),
        )
        .select("period", "total_flights", "avg_arr_delay", "cancellation_rate_pct", "ontime_pct")
        .sort("avg_arr_delay", descending=True, nulls_last=True)
    )

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()['holiday-impact'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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