TPC-DS Q36 — Gross Margin ROLLUP with Rank

TPC-DS benchmark · all TPC-DS queries

Computes gross margin with ROLLUP and hierarchical ranking by state

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(ss_net_profit) / SUM(ss_ext_sales_price) AS gross_margin,
  i_category,
  i_class,
  GROUPING(i_category) + GROUPING(i_class) AS lochierarchy,
  RANK() OVER (
    PARTITION BY GROUPING(i_category) + GROUPING(i_class), CASE WHEN GROUPING(i_class) = 0 THEN i_category END
    ORDER BY SUM(ss_net_profit) / SUM(ss_ext_sales_price) ASC
  ) AS rank_within_parent
FROM store_sales, date_dim AS d1, item, store
WHERE
  d1.d_year = 2000
  AND d1.d_date_sk = ss_sold_date_sk
  AND i_item_sk = ss_item_sk
  AND s_store_sk = ss_store_sk
  AND s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
GROUP BY
  ROLLUP (
    i_category,
    i_class
  )
ORDER BY
  lochierarchy DESC,
  CASE WHEN lochierarchy = 0 THEN i_category END,
  rank_within_parent
LIMIT 100

Representative DataFrame

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

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

    params = get_parameters(36)
    year = params.get("year", 2000)
    states = params.get("states", ["TN"])

    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_year") == lit(year)) & col("s_state").is_in(states))
    )

    agg_exprs = [
        col("ss_net_profit").sum().alias("sum_profit"),
        col("ss_ext_sales_price").sum().alias("sum_sales"),
    ]

    rollup_result = expand_rollup_expression(
        base,
        group_cols=["i_category", "i_class"],
        agg_exprs=agg_exprs,
        ctx=ctx,
        count_sources={"sum_profit": "ss_net_profit", "sum_sales": "ss_ext_sales_price"},
    )

    profit, sales = col("sum_profit"), col("sum_sales")
    zero_sales_margin = (
        ctx.when(profit == lit(0))
        .then(lit(float("nan")))
        .when(profit > lit(0))
        .then(lit(float("inf")))
        .otherwise(lit(float("-inf")))
    )
    margin = (
        ctx.when(profit.is_null() | sales.is_null())
        .then(lit(None))
        .when(sales == lit(0))
        .then(zero_sales_margin)
        .otherwise(profit / sales)
    )
    result = rollup_result.with_columns(margin.alias("gross_margin"))

    lochierarchy_expr = lochierarchy_expression("grouping_id", 2, ctx=ctx)
    result = result.with_columns(lochierarchy_expr.alias("lochierarchy"))

    result = result.with_columns(
        ctx.when(col("grouping_id") & 1 == 0).then(col("i_category")).otherwise(lit(None)).alias("partition_key")
    )

    result = result.with_columns(
        _sql_rank_expression(ctx, "gross_margin", ["lochierarchy", "partition_key"], descending=False).alias(
            "rank_within_parent"
        )
    )

    return _sort_rollup_hierarchy_expression(ctx, result, "gross_margin")

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

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

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

Source

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