TPC-DS Q18 — Catalog Sales Demographics ROLLUP

TPC-DS benchmark · all TPC-DS queries

Catalog sales with demographics aggregated with ROLLUP on geography

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  i_item_id,
  ca_country,
  ca_state,
  ca_county,
  AVG(CAST(cs_quantity AS DECIMAL(12, 2))) AS agg1,
  AVG(CAST(cs_list_price AS DECIMAL(12, 2))) AS agg2,
  AVG(CAST(cs_coupon_amt AS DECIMAL(12, 2))) AS agg3,
  AVG(CAST(cs_sales_price AS DECIMAL(12, 2))) AS agg4,
  AVG(CAST(cs_net_profit AS DECIMAL(12, 2))) AS agg5,
  AVG(CAST(c_birth_year AS DECIMAL(12, 2))) AS agg6,
  AVG(CAST(cd1.cd_dep_count AS DECIMAL(12, 2))) AS agg7
FROM catalog_sales, customer_demographics AS cd1, customer_demographics AS cd2, customer, customer_address, date_dim, item
WHERE
  cs_sold_date_sk = d_date_sk
  AND cs_item_sk = i_item_sk
  AND cs_bill_cdemo_sk = cd1.cd_demo_sk
  AND cs_bill_customer_sk = c_customer_sk
  AND cd1.cd_gender = 'M'
  AND cd1.cd_education_status = 'College'
  AND c_current_cdemo_sk = cd2.cd_demo_sk
  AND c_current_addr_sk = ca_address_sk
  AND c_birth_month IN (9, 5, 12, 4, 1, 10)
  AND d_year = 2001
  AND ca_state IN ('ND', 'WI', 'AL', 'NC', 'OK', 'MS', 'TN')
GROUP BY
  ROLLUP (
    i_item_id,
    ca_country,
    ca_state,
    ca_county
  )
ORDER BY
  ca_country,
  ca_state,
  ca_county,
  i_item_id
LIMIT 100

Representative DataFrame

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

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

    params = get_parameters(18)
    year = params.get("year", 2001)
    states = params.get("states", ["ND", "WI", "AL", "NC", "OK", "MS", "TN"])
    cd_gender = params.get("cd_gender", "M")
    cd_education_status = params.get("cd_education_status", "College")
    birth_months = params.get("birth_months", [9, 5, 12, 4, 1, 10])

    catalog_sales, customer_demographics, customer, customer_address, date_dim, item = _tables(
        ctx, "catalog_sales", "customer_demographics", "customer", "customer_address", "date_dim", "item"
    )
    col = ctx.col
    lit = ctx.lit

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

    cd1 = customer_demographics.filter(
        (col("cd_gender") == lit(cd_gender)) & (col("cd_education_status") == lit(cd_education_status))
    )

    ca_filtered = customer_address.filter(col("ca_state").is_in(states))

    customer_filtered = customer.filter(col("c_birth_month").is_in(birth_months))

    cs_joined = (
        catalog_sales.join(date_filtered, left_on="cs_sold_date_sk", right_on="d_date_sk")
        .join(item, left_on="cs_item_sk", right_on="i_item_sk")
        .join(cd1, left_on="cs_bill_cdemo_sk", right_on="cd_demo_sk")
        .join(customer_filtered, left_on="cs_bill_customer_sk", right_on="c_customer_sk")
        .join(ca_filtered, left_on="c_current_addr_sk", right_on="ca_address_sk")
        .join(
            customer_demographics.rename({"cd_demo_sk": "cd2_demo_sk", "cd_dep_count": "cd2_dep_count"}),
            left_on="c_current_cdemo_sk",
            right_on="cd2_demo_sk",
        )
    )

    agg_exprs = [
        col("cs_quantity").cast_float64().mean().alias("agg1"),
        col("cs_list_price").cast_float64().mean().alias("agg2"),
        col("cs_coupon_amt").cast_float64().mean().alias("agg3"),
        col("cs_sales_price").cast_float64().mean().alias("agg4"),
        col("cs_net_profit").cast_float64().mean().alias("agg5"),
        col("c_birth_year").cast_float64().mean().alias("agg6"),
        col("cd_dep_count").cast_float64().mean().alias("agg7"),
    ]

    group_cols = ["i_item_id", "ca_country", "ca_state", "ca_county"]
    result = expand_rollup_expression(cs_joined, group_cols, agg_exprs, ctx)

    sort_cols = ["ca_country", "ca_state", "ca_county", "i_item_id"]
    out_cols = [*group_cols, "agg1", "agg2", "agg3", "agg4", "agg5", "agg6", "agg7"]
    return result.select(out_cols).sort(sort_cols, 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['18'])

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

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

Source

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