TPC-DS Q77 — Three-Channel Sales-Returns with Separate CTEs

TPC-DS benchmark · all TPC-DS queries

Aggregates sales and returns separately for each channel with ROLLUP

Representative SQL

Rendered for DataFusion with default parameters.

WITH ss AS (
  SELECT
    s_store_sk,
    SUM(ss_ext_sales_price) AS sales,
    SUM(ss_net_profit) AS profit
  FROM store_sales, date_dim, store
  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
  GROUP BY
    s_store_sk
), sr AS (
  SELECT
    s_store_sk,
    SUM(sr_return_amt) AS returns,
    SUM(sr_net_loss) AS profit_loss
  FROM store_returns, date_dim, store
  WHERE
    sr_returned_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 sr_store_sk = s_store_sk
  GROUP BY
    s_store_sk
), cs AS (
  SELECT
    cs_call_center_sk,
    SUM(cs_ext_sales_price) AS sales,
    SUM(cs_net_profit) AS profit
  FROM catalog_sales, date_dim
  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'
    )
  GROUP BY
    cs_call_center_sk
), cr AS (
  SELECT
    cr_call_center_sk,
    SUM(cr_return_amount) AS returns,
    SUM(cr_net_loss) AS profit_loss
  FROM catalog_returns, date_dim
  WHERE
    cr_returned_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'
    )
  GROUP BY
    cr_call_center_sk
), ws AS (
  SELECT
    wp_web_page_sk,
    SUM(ws_ext_sales_price) AS sales,
    SUM(ws_net_profit) AS profit
  FROM web_sales, date_dim, web_page
  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_page_sk = wp_web_page_sk
  GROUP BY
    wp_web_page_sk
), wr AS (
  SELECT
    wp_web_page_sk,
    SUM(wr_return_amt) AS returns,
    SUM(wr_net_loss) AS profit_loss
  FROM web_returns, date_dim, web_page
  WHERE
    wr_returned_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 wr_web_page_sk = wp_web_page_sk
  GROUP BY
    wp_web_page_sk
)
SELECT
  channel,
  id,
  SUM(sales) AS sales,
  SUM(returns) AS returns,
  SUM(profit) AS profit
FROM (
  SELECT
    'store channel' AS channel,
    ss.s_store_sk AS id,
    sales,
    COALESCE(returns, 0) AS returns,
    (
      profit - COALESCE(profit_loss, 0)
    ) AS profit
  FROM ss
  LEFT JOIN sr
    ON ss.s_store_sk = sr.s_store_sk
  UNION ALL
  SELECT
    'catalog channel' AS channel,
    cs_call_center_sk AS id,
    sales,
    returns,
    (
      profit - profit_loss
    ) AS profit
  FROM cs, cr
  UNION ALL
  SELECT
    'web channel' AS channel,
    ws.wp_web_page_sk AS id,
    sales,
    COALESCE(returns, 0) AS returns,
    (
      profit - COALESCE(profit_loss, 0)
    ) AS profit
  FROM ws
  LEFT JOIN wr
    ON ws.wp_web_page_sk = wr.wp_web_page_sk
) AS x
GROUP BY
  ROLLUP (
    channel,
    id
  )
ORDER BY
  channel,
  id
LIMIT 100

Representative DataFrame

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

def q77_expression_impl(ctx: DataFrameContext) -> Any:
    date_filtered = _date_window_expression(ctx, 77, "1998-08-04")
    combined = ctx.concat(
        [
            _q77_expression_channel(ctx, date_filtered, channel, sales, returns, how)
            for channel, sales, returns, how in _Q77_EXPRESSION_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['77'])

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

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

Source

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