TPC-DS Q9 — Store Sales Quantity Bucket Analysis

TPC-DS benchmark · all TPC-DS queries

Computes conditional averages across quantity buckets

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  CASE
    WHEN (
      SELECT
        COUNT(*)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 1 AND 20
    ) > 25437
    THEN (
      SELECT
        AVG(ss_ext_discount_amt)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 1 AND 20
    )
    ELSE (
      SELECT
        AVG(ss_net_profit)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 1 AND 20
    )
  END AS bucket1,
  CASE
    WHEN (
      SELECT
        COUNT(*)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 21 AND 40
    ) > 22746
    THEN (
      SELECT
        AVG(ss_ext_discount_amt)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 21 AND 40
    )
    ELSE (
      SELECT
        AVG(ss_net_profit)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 21 AND 40
    )
  END AS bucket2,
  CASE
    WHEN (
      SELECT
        COUNT(*)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 41 AND 60
    ) > 9387
    THEN (
      SELECT
        AVG(ss_ext_discount_amt)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 41 AND 60
    )
    ELSE (
      SELECT
        AVG(ss_net_profit)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 41 AND 60
    )
  END AS bucket3,
  CASE
    WHEN (
      SELECT
        COUNT(*)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 61 AND 80
    ) > 10098
    THEN (
      SELECT
        AVG(ss_ext_discount_amt)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 61 AND 80
    )
    ELSE (
      SELECT
        AVG(ss_net_profit)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 61 AND 80
    )
  END AS bucket4,
  CASE
    WHEN (
      SELECT
        COUNT(*)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 81 AND 100
    ) > 18213
    THEN (
      SELECT
        AVG(ss_ext_discount_amt)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 81 AND 100
    )
    ELSE (
      SELECT
        AVG(ss_net_profit)
      FROM store_sales
      WHERE
        ss_quantity BETWEEN 81 AND 100
    )
  END AS bucket5
FROM reason
WHERE
  r_reason_sk = 1

Representative DataFrame

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

def q9_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(9)
    quantity_ranges = params.get("quantity_ranges", [(1, 20), (21, 40), (41, 60), (61, 80), (81, 100)])
    thresholds = params.get("thresholds", [25437, 22746, 9387, 10098, 18213])
    agg_then = params.get("agg_then", "ss_ext_discount_amt")
    agg_else = params.get("agg_else", "ss_net_profit")

    store_sales, reason = _tables(ctx, "store_sales", "reason")

    col = ctx.col
    lit = ctx.lit

    buckets = []
    for i, (q_min, q_max) in enumerate(quantity_ranges):
        bucket = store_sales.filter((col("ss_quantity") >= lit(q_min)) & (col("ss_quantity") <= lit(q_max)))
        cnt_df = bucket.select(ctx.len().alias("cnt"))
        cnt = cnt_df.scalar(0, 0) if hasattr(cnt_df, "scalar") else len(bucket)

        threshold = thresholds[i] if i < len(thresholds) else 1000

        if cnt > threshold:
            avg_val = bucket.select(ctx.mean(agg_then).alias("avg_val"))
        else:
            avg_val = bucket.select(ctx.mean(agg_else).alias("avg_val"))

        if hasattr(avg_val, "scalar"):
            val = avg_val.scalar(0, 0)
        else:
            val = avg_val.to_numpy()[0, 0] if len(avg_val) > 0 else None

        buckets.append(val)

    return reason.filter(col("r_reason_sk") == lit(1)).select(
        lit(buckets[0]).alias("bucket1") if len(buckets) > 0 else lit(None).alias("bucket1"),
        lit(buckets[1]).alias("bucket2") if len(buckets) > 1 else lit(None).alias("bucket2"),
        lit(buckets[2]).alias("bucket3") if len(buckets) > 2 else lit(None).alias("bucket3"),
        lit(buckets[3]).alias("bucket4") if len(buckets) > 3 else lit(None).alias("bucket4"),
        lit(buckets[4]).alias("bucket5") if len(buckets) > 4 else lit(None).alias("bucket5"),
    )

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 TPCDS

bench = TPCDS(scale_factor=1.0)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['9'])

Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:

print(bench.get_query(9, scale_factor=1.0, seed=42, dialect="datafusion"))

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('tpcds', '9', family="expression").source)  # or family="pandas"

Source

Query text: _sources/tpc-ds/query_templates/query9.tpl