TPC-DS Q24 — Store Sales Returns by Color

TPC-DS benchmark · all TPC-DS queries

Store sales with returns filtered by market and color threshold

Representative SQL

Rendered for DataFusion with default parameters.

WITH ssales AS (
  SELECT
    c_last_name,
    c_first_name,
    s_store_name,
    ca_state,
    s_state,
    i_color,
    i_current_price,
    i_manager_id,
    i_units,
    i_size,
    SUM(ss_sales_price) AS netpaid
  FROM store_sales, store_returns, store, item, customer, customer_address
  WHERE
    ss_ticket_number = sr_ticket_number
    AND ss_item_sk = sr_item_sk
    AND ss_customer_sk = c_customer_sk
    AND ss_item_sk = i_item_sk
    AND ss_store_sk = s_store_sk
    AND c_current_addr_sk = ca_address_sk
    AND c_birth_country <> UPPER(ca_country)
    AND s_zip = ca_zip
    AND s_market_id = 7
  GROUP BY
    c_last_name,
    c_first_name,
    s_store_name,
    ca_state,
    s_state,
    i_color,
    i_current_price,
    i_manager_id,
    i_units,
    i_size
)
SELECT
  c_last_name,
  c_first_name,
  s_store_name,
  SUM(netpaid) AS paid
FROM ssales
WHERE
  i_color = 'orchid'
GROUP BY
  c_last_name,
  c_first_name,
  s_store_name
HAVING
  SUM(netpaid) > (
    SELECT
      0.05 * AVG(netpaid)
    FROM ssales
  )
ORDER BY
  c_last_name,
  c_first_name,
  s_store_name

Representative DataFrame

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

def q24_expression_impl(ctx: DataFrameContext) -> Any:
    return _q24_expression(ctx, get_parameters(24).get("color", "orchid"))

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

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

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

Source

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