Flight Data — Seasonal Performance Trends

Flight Data benchmark · all Flight Data queries

Monthly flight volume and performance aggregated across all years

Representative SQL

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

SELECT
  f.month,
  CASE f.month
    WHEN 1
    THEN 'January'
    WHEN 2
    THEN 'February'
    WHEN 3
    THEN 'March'
    WHEN 4
    THEN 'April'
    WHEN 5
    THEN 'May'
    WHEN 6
    THEN 'June'
    WHEN 7
    THEN 'July'
    WHEN 8
    THEN 'August'
    WHEN 9
    THEN 'September'
    WHEN 10
    THEN 'October'
    WHEN 11
    THEN 'November'
    WHEN 12
    THEN 'December'
  END AS month_name,
  COUNT(*) AS total_flights,
  SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) AS cancelled_count,
  ROUND(100.0 * SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate_pct,
  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 = 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
  f.month
ORDER BY
  f.month

Representative DataFrame

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

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

    return (
        flights.with_columns(
            ctx.when(col("month") == lit(1))
            .then(lit("January"))
            .when(col("month") == lit(2))
            .then(lit("February"))
            .when(col("month") == lit(3))
            .then(lit("March"))
            .when(col("month") == lit(4))
            .then(lit("April"))
            .when(col("month") == lit(5))
            .then(lit("May"))
            .when(col("month") == lit(6))
            .then(lit("June"))
            .when(col("month") == lit(7))
            .then(lit("July"))
            .when(col("month") == lit(8))
            .then(lit("August"))
            .when(col("month") == lit(9))
            .then(lit("September"))
            .when(col("month") == lit(10))
            .then(lit("October"))
            .when(col("month") == lit(11))
            .then(lit("November"))
            .otherwise(lit("December"))
            .alias("month_name")
        )
        .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("month", "month_name")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_cancelled").sum().alias("cancelled_count"),
            col("_arr_nc").mean().alias("_avg_arr"),
            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, lit(100.0) * col("cancelled_count") / col("total_flights"), 2).alias(
                "cancellation_rate_pct"
            ),
            _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(
            "month",
            "month_name",
            "total_flights",
            "cancelled_count",
            "cancellation_rate_pct",
            "avg_arr_delay",
            "ontime_pct",
        )
        .sort("month")
    )

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()['seasonal-trends'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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