TPC-DS Q27 — Store Sales Demographics ROLLUP

TPC-DS benchmark · all TPC-DS queries

Store sales with demographics aggregated with ROLLUP on item/state

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_item_id,
  s_state,
  GROUPING(s_state) AS g_state,
  AVG(ss_quantity) AS agg1,
  AVG(ss_list_price) AS agg2,
  AVG(ss_coupon_amt) AS agg3,
  AVG(ss_sales_price) AS agg4
FROM store_sales, customer_demographics, 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 ss_cdemo_sk = cd_demo_sk
  AND cd_gender = 'F'
  AND cd_marital_status = 'W'
  AND cd_education_status = 'Primary'
  AND d_year = 1998
  AND s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN')
GROUP BY
  ROLLUP (
    i_item_id,
    s_state
  )
ORDER BY
  i_item_id,
  s_state
LIMIT 100

Representative DataFrame

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

def q27_expression_impl(ctx: DataFrameContext) -> Any:
    from .rollup_helper import expand_rollup_expression

    params = get_parameters(27)
    year = params.get("year", 1998)
    gender = params.get("gender", "F")
    marital_status = params.get("marital_status", "W")
    education = params.get("education", "Primary")
    states = params.get("states", ["TN"])

    store_sales, customer_demographics, date_dim, store, item = _tables(
        ctx, "store_sales", "customer_demographics", "date_dim", "store", "item"
    )
    col = ctx.col
    lit = ctx.lit

    date_filtered = date_dim.filter(col("d_year") == lit(year))

    cd_filtered = customer_demographics.filter(
        (col("cd_gender") == lit(gender))
        & (col("cd_marital_status") == lit(marital_status))
        & (col("cd_education_status") == lit(education))
    )

    store_filtered = store.filter(col("s_state").is_in(states))

    ss_joined = (
        store_sales.join(date_filtered, 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_filtered, left_on="ss_store_sk", right_on="s_store_sk")
        .join(cd_filtered, left_on="ss_cdemo_sk", right_on="cd_demo_sk")
    )

    agg_exprs = [
        col("ss_quantity").mean().alias("agg1"),
        col("ss_list_price").mean().alias("agg2"),
        col("ss_coupon_amt").mean().alias("agg3"),
        col("ss_sales_price").mean().alias("agg4"),
    ]

    group_cols = ["i_item_id", "s_state"]
    result = expand_rollup_expression(ss_joined, group_cols, agg_exprs, ctx)

    return (
        result.with_columns(ctx.when(col("grouping_id") == lit(0)).then(lit(0)).otherwise(lit(1)).alias("g_state"))
        .select(["i_item_id", "s_state", "g_state", "agg1", "agg2", "agg3", "agg4"])
        .sort(["i_item_id", "s_state"], 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['27'])

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

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

Source

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