TPC-DS Q80 — Three-Channel Sales-Returns with ROLLUP

TPC-DS benchmark · all TPC-DS queries

Aggregates sales and returns across channels with ROLLUP for subtotals

Representative SQL

Rendered for DataFusion with default parameters.

WITH ssr AS (
  SELECT
    s_store_id AS store_id,
    SUM(ss_ext_sales_price) AS sales,
    SUM(COALESCE(sr_return_amt, 0)) AS returns,
    SUM(ss_net_profit - COALESCE(sr_net_loss, 0)) AS profit
  FROM store_sales
  LEFT OUTER JOIN store_returns
    ON (
      ss_item_sk = sr_item_sk AND ss_ticket_number = sr_ticket_number
    ), date_dim, store, item, promotion
  WHERE
    ss_sold_date_sk = d_date_sk
    AND d_date BETWEEN CAST('1998-08-04' AS DATE) AND (
      CAST('1998-08-04' AS DATE) + INTERVAL '30 DAY'
    )
    AND ss_store_sk = s_store_sk
    AND ss_item_sk = i_item_sk
    AND i_current_price > 50
    AND ss_promo_sk = p_promo_sk
    AND p_channel_tv = 'N'
  GROUP BY
    s_store_id
), csr AS (
  SELECT
    cp_catalog_page_id AS catalog_page_id,
    SUM(cs_ext_sales_price) AS sales,
    SUM(COALESCE(cr_return_amount, 0)) AS returns,
    SUM(cs_net_profit - COALESCE(cr_net_loss, 0)) AS profit
  FROM catalog_sales
  LEFT OUTER JOIN catalog_returns
    ON (
      cs_item_sk = cr_item_sk AND cs_order_number = cr_order_number
    ), date_dim, catalog_page, item, promotion
  WHERE
    cs_sold_date_sk = d_date_sk
    AND d_date BETWEEN CAST('1998-08-04' AS DATE) AND (
      CAST('1998-08-04' AS DATE) + INTERVAL '30 DAY'
    )
    AND cs_catalog_page_sk = cp_catalog_page_sk
    AND cs_item_sk = i_item_sk
    AND i_current_price > 50
    AND cs_promo_sk = p_promo_sk
    AND p_channel_tv = 'N'
  GROUP BY
    cp_catalog_page_id
), wsr AS (
  SELECT
    web_site_id,
    SUM(ws_ext_sales_price) AS sales,
    SUM(COALESCE(wr_return_amt, 0)) AS returns,
    SUM(ws_net_profit - COALESCE(wr_net_loss, 0)) AS profit
  FROM web_sales
  LEFT OUTER JOIN web_returns
    ON (
      ws_item_sk = wr_item_sk AND ws_order_number = wr_order_number
    ), date_dim, web_site, item, promotion
  WHERE
    ws_sold_date_sk = d_date_sk
    AND d_date BETWEEN CAST('1998-08-04' AS DATE) AND (
      CAST('1998-08-04' AS DATE) + INTERVAL '30 DAY'
    )
    AND ws_web_site_sk = web_site_sk
    AND ws_item_sk = i_item_sk
    AND i_current_price > 50
    AND ws_promo_sk = p_promo_sk
    AND p_channel_tv = 'N'
  GROUP BY
    web_site_id
)
SELECT
  channel,
  id,
  SUM(sales) AS sales,
  SUM(returns) AS returns,
  SUM(profit) AS profit
FROM (
  SELECT
    'store channel' AS channel,
    'store' || store_id AS id,
    sales,
    returns,
    profit
  FROM ssr
  UNION ALL
  SELECT
    'catalog channel' AS channel,
    'catalog_page' || catalog_page_id AS id,
    sales,
    returns,
    profit
  FROM csr
  UNION ALL
  SELECT
    'web channel' AS channel,
    'web_site' || web_site_id AS id,
    sales,
    returns,
    profit
  FROM wsr
) AS x
GROUP BY
  ROLLUP (
    channel,
    id
  )
ORDER BY
  channel,
  id
LIMIT 100

Representative DataFrame

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

def q80_expression_impl(ctx: DataFrameContext) -> Any:
    col = ctx.col
    lit = ctx.lit
    date_filtered = _date_window_expression(ctx, 80, "1998-08-04")
    item_filtered = ctx.get_table("item").filter(col("i_current_price") > lit(50))
    promo_filtered = ctx.get_table("promotion").filter(col("p_channel_tv") == lit("N"))
    combined = ctx.concat(
        [
            _q80_channel_expression(ctx, date_filtered, item_filtered, promo_filtered, spec)
            for spec in _q80_channel_specs()
        ]
    )
    return _sales_returns_rollup_expression(ctx, combined)

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

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

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

Source

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