TPC-DS Q70 — Store Sales ROLLUP with Subquery Filter

TPC-DS benchmark · all TPC-DS queries

State/county ROLLUP with top-5 states subquery filter

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(ss_net_profit) AS total_sum,
  s_state,
  s_county,
  GROUPING(s_state) + GROUPING(s_county) AS lochierarchy,
  RANK() OVER (
    PARTITION BY GROUPING(s_state) + GROUPING(s_county), CASE WHEN GROUPING(s_county) = 0 THEN s_state END
    ORDER BY SUM(ss_net_profit) DESC
  ) AS rank_within_parent
FROM store_sales, date_dim AS d1, store
WHERE
  d1.d_month_seq BETWEEN 1212 AND 1212 + 11
  AND d1.d_date_sk = ss_sold_date_sk
  AND s_store_sk = ss_store_sk
  AND s_state IN (
    SELECT
      s_state
    FROM (
      SELECT
        s_state AS s_state,
        RANK() OVER (PARTITION BY s_state ORDER BY SUM(ss_net_profit) DESC) AS ranking
      FROM store_sales, store, date_dim
      WHERE
        d_month_seq BETWEEN 1212 AND 1212 + 11
        AND d_date_sk = ss_sold_date_sk
        AND s_store_sk = ss_store_sk
      GROUP BY
        s_state
    ) AS tmp1
    WHERE
      ranking <= 5
  )
GROUP BY
  ROLLUP (
    s_state,
    s_county
  )
ORDER BY
  lochierarchy DESC,
  CASE WHEN lochierarchy = 0 THEN s_state END,
  rank_within_parent
LIMIT 100

Representative DataFrame

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

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

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

    col = ctx.col
    lit = ctx.lit

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

    dates = date_dim.filter(col("d_month_seq").is_between(dms, dms + 11))

    base = (
        store_sales.join(dates, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(store, left_on="ss_store_sk", right_on="s_store_sk")
        .filter(col("s_state").is_not_null())
    )

    agg_exprs = [col("ss_net_profit").sum().alias("total_sum")]
    rollup_result = expand_rollup_expression(
        base, ["s_state", "s_county"], agg_exprs, ctx, count_sources={"total_sum": "ss_net_profit"}
    )
    rollup_result = rollup_result.with_columns(col("total_sum").round(2).alias("total_sum"))

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

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

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

    result = result.with_columns(
        ctx.when(col("lochierarchy") == 0).then(col("s_state")).otherwise(lit(None)).alias("order_state")
    )

    return (
        _sort_null_largest_expression(
            ctx, result, ["lochierarchy", "order_state", "rank_within_parent"], [True, False, False]
        )
        .select(
            col("total_sum"),
            col("s_state"),
            col("s_county"),
            col("lochierarchy"),
            col("rank_within_parent"),
        )
        .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['70'])

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

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

Source

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