TPC-DS Q91 — Call Center Returns Analysis

TPC-DS benchmark · all TPC-DS queries

Reports catalog returns by call center with demographic filtering

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  cc_call_center_id AS Call_Center,
  cc_name AS Call_Center_Name,
  cc_manager AS Manager,
  SUM(cr_net_loss) AS Returns_Loss
FROM call_center, catalog_returns, date_dim, customer, customer_address, customer_demographics, household_demographics
WHERE
  cr_call_center_sk = cc_call_center_sk
  AND cr_returned_date_sk = d_date_sk
  AND cr_returning_customer_sk = c_customer_sk
  AND cd_demo_sk = c_current_cdemo_sk
  AND hd_demo_sk = c_current_hdemo_sk
  AND ca_address_sk = c_current_addr_sk
  AND d_year = 1999
  AND d_moy = 11
  AND (
    (
      cd_marital_status = 'M' AND cd_education_status = 'Unknown'
    )
    OR (
      cd_marital_status = 'W' AND cd_education_status = 'Advanced Degree'
    )
  )
  AND hd_buy_potential LIKE '0-500%'
  AND ca_gmt_offset = -7
GROUP BY
  cc_call_center_id,
  cc_name,
  cc_manager,
  cd_marital_status,
  cd_education_status
ORDER BY
  SUM(cr_net_loss) DESC

Representative DataFrame

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

def impl(ctx: DataFrameContext) -> Any:
        return engine(ctx, spec)

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

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

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

Source

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