TPC-DS Q67 — Extensive ROLLUP with Rank

TPC-DS benchmark · all TPC-DS queries

8-column ROLLUP on item/date/store with hierarchical ranking

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  *
FROM (
  SELECT
    i_category,
    i_class,
    i_brand,
    i_product_name,
    d_year,
    d_qoy,
    d_moy,
    s_store_id,
    sumsales,
    RANK() OVER (PARTITION BY i_category ORDER BY sumsales DESC) AS rk
  FROM (
    SELECT
      i_category,
      i_class,
      i_brand,
      i_product_name,
      d_year,
      d_qoy,
      d_moy,
      s_store_id,
      SUM(COALESCE(ss_sales_price * ss_quantity, 0)) AS sumsales
    FROM store_sales, date_dim, store, item
    WHERE
      ss_sold_date_sk = d_date_sk
      AND ss_item_sk = i_item_sk
      AND ss_store_sk = s_store_sk
      AND d_month_seq BETWEEN 1212 AND 1212 + 11
    GROUP BY
      ROLLUP (
        i_category,
        i_class,
        i_brand,
        i_product_name,
        d_year,
        d_qoy,
        d_moy,
        s_store_id
      )
  ) AS dw1
) AS dw2
WHERE
  rk <= 100
ORDER BY
  i_category,
  i_class,
  i_brand,
  i_product_name,
  d_year,
  d_qoy,
  d_moy,
  s_store_id,
  sumsales,
  rk
LIMIT 100

Representative DataFrame

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

def q67_expression_impl(ctx: DataFrameContext) -> Any:
    from benchbox.core.tpcds.dataframe_queries.rollup_helper import expand_rollup_expression

    params = get_parameters(67)
    dms = params.get("dms", 1212)

    col = ctx.col
    lit = ctx.lit

    store_sales, date_dim, item, store = _tables(ctx, "store_sales", "date_dim", "item", "store")

    base = (
        store_sales.join(date_dim, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(item, left_on="ss_item_sk", right_on="i_item_sk")
        .join(store, left_on="ss_store_sk", right_on="s_store_sk")
        .filter(col("d_month_seq").is_between(dms, dms + 11))
        .with_columns(
            ctx.coalesce((col("ss_sales_price") * lit(100)).round(0).cast_int64() * col("ss_quantity"), lit(0)).alias(
                "sales_amount_cents"
            )
        )
    )

    group_cols = [
        "i_category",
        "i_class",
        "i_brand",
        "i_product_name",
        "d_year",
        "d_qoy",
        "d_moy",
        "s_store_id",
    ]

    agg_exprs = [col("sales_amount_cents").sum().alias("sumsales_cents")]

    rollup_result = expand_rollup_expression(
        base, group_cols, agg_exprs, ctx, count_sources={"sumsales_cents": "sales_amount_cents"}
    )

    result = rollup_result.with_columns(
        _sql_rank_expression(ctx, "sumsales_cents", ["i_category"], descending=True).alias("rk")
    ).with_columns((col("sumsales_cents").cast_float64() / lit(100)).alias("sumsales"))

    return (
        result.filter(col("rk") <= 100)
        .select(
            col("i_category"),
            col("i_class"),
            col("i_brand"),
            col("i_product_name"),
            col("d_year"),
            col("d_qoy"),
            col("d_moy"),
            col("s_store_id"),
            col("sumsales"),
            col("rk"),
        )
        .sort(
            [
                "i_category",
                "i_class",
                "i_brand",
                "i_product_name",
                "d_year",
                "d_qoy",
                "d_moy",
                "s_store_id",
                "sumsales",
                "rk",
            ],
            nulls_last=True,
        )
        .head(100)
    )

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['67'])

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

print(bench.get_query(67, 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', '67', family="expression").source)  # or family="pandas"

Source

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