Flight Data — Cancellation Rates by Carrier and Cause

Flight Data benchmark · all Flight Data queries

Cancellation frequency and reason breakdown by carrier

Representative SQL

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

SELECT
  f.reporting_airline,
  a.name AS airline_name,
  COUNT(*) AS total_flights,
  SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) AS total_cancelled,
  ROUND(100.0 * SUM(CASE WHEN f.cancelled = 1 THEN 1 ELSE 0 END) / COUNT(*), 3) AS cancellation_rate_pct,
  SUM(CASE WHEN f.cancellation_code = 'A' THEN 1 ELSE 0 END) AS carrier_cancellations,
  SUM(CASE WHEN f.cancellation_code = 'B' THEN 1 ELSE 0 END) AS weather_cancellations,
  SUM(CASE WHEN f.cancellation_code = 'C' THEN 1 ELSE 0 END) AS nas_cancellations,
  SUM(CASE WHEN f.cancellation_code = 'D' THEN 1 ELSE 0 END) AS security_cancellations
FROM flights AS f
LEFT JOIN airlines AS a
  ON f.reporting_airline = a.code
WHERE
  f.flight_date >= '2021-08-01' AND f.flight_date < '2025-01-01'
GROUP BY
  f.reporting_airline,
  a.name
HAVING
  COUNT(*) >= 100
ORDER BY
  cancellation_rate_pct DESC,
  f.reporting_airline

Representative DataFrame

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

def cancellation_rate_expression_impl(ctx: DataFrameContext) -> Any:
    flights = ctx.get_table("flights")
    col, lit = ctx.col, ctx.lit

    return (
        flights.join(_airline_names(ctx), left_on="reporting_airline", right_on="a_code", how="left")
        .filter(_date_condition(col, lit))
        .with_columns(
            (col("cancelled") == lit(1)).cast(int).alias("_cancelled"),
            (col("cancellation_code") == lit("A")).cast(int).alias("_code_a"),
            (col("cancellation_code") == lit("B")).cast(int).alias("_code_b"),
            (col("cancellation_code") == lit("C")).cast(int).alias("_code_c"),
            (col("cancellation_code") == lit("D")).cast(int).alias("_code_d"),
        )
        .group_by("reporting_airline", "airline_name")
        .agg(
            col("flight_id").count().alias("total_flights"),
            col("_cancelled").sum().alias("total_cancelled"),
            col("_code_a").sum().alias("carrier_cancellations"),
            col("_code_b").sum().alias("weather_cancellations"),
            col("_code_c").sum().alias("nas_cancellations"),
            col("_code_d").sum().alias("security_cancellations"),
        )
        .filter(col("total_flights") >= lit(100))
        .with_columns(
            _round_half_away(ctx, lit(100.0) * col("total_cancelled") / col("total_flights"), 3).alias(
                "cancellation_rate_pct"
            )
        )
        .select(
            "reporting_airline",
            "airline_name",
            "total_flights",
            "total_cancelled",
            "cancellation_rate_pct",
            "carrier_cancellations",
            "weather_cancellations",
            "nas_cancellations",
            "security_cancellations",
        )
        .sort(["cancellation_rate_pct", "reporting_airline"], descending=[True, False])
    )

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()['cancellation-rate'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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