TPC-DS Q50 — Store Sales Returns Analysis

TPC-DS benchmark · all TPC-DS queries

Analyzes store sales return patterns

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_store_name,
  s_company_id,
  s_street_number,
  s_street_name,
  s_street_type,
  s_suite_number,
  s_city,
  s_county,
  s_state,
  s_zip,
  SUM(CASE WHEN (
    sr_returned_date_sk - ss_sold_date_sk <= 30
  ) THEN 1 ELSE 0 END) AS "30 days",
  SUM(
    CASE
      WHEN (
        sr_returned_date_sk - ss_sold_date_sk > 30
      )
      AND (
        sr_returned_date_sk - ss_sold_date_sk <= 60
      )
      THEN 1
      ELSE 0
    END
  ) AS "31-60 days",
  SUM(
    CASE
      WHEN (
        sr_returned_date_sk - ss_sold_date_sk > 60
      )
      AND (
        sr_returned_date_sk - ss_sold_date_sk <= 90
      )
      THEN 1
      ELSE 0
    END
  ) AS "61-90 days",
  SUM(
    CASE
      WHEN (
        sr_returned_date_sk - ss_sold_date_sk > 90
      )
      AND (
        sr_returned_date_sk - ss_sold_date_sk <= 120
      )
      THEN 1
      ELSE 0
    END
  ) AS "91-120 days",
  SUM(CASE WHEN (
    sr_returned_date_sk - ss_sold_date_sk > 120
  ) THEN 1 ELSE 0 END) AS ">120 days"
FROM store_sales, store_returns, store, date_dim AS d1, date_dim AS d2
WHERE
  d2.d_year = 2000
  AND d2.d_moy = 9
  AND ss_ticket_number = sr_ticket_number
  AND ss_item_sk = sr_item_sk
  AND ss_sold_date_sk = d1.d_date_sk
  AND sr_returned_date_sk = d2.d_date_sk
  AND ss_customer_sk = sr_customer_sk
  AND ss_store_sk = s_store_sk
GROUP BY
  s_store_name,
  s_company_id,
  s_street_number,
  s_street_name,
  s_street_type,
  s_suite_number,
  s_city,
  s_county,
  s_state,
  s_zip
ORDER BY
  s_store_name,
  s_company_id,
  s_street_number,
  s_street_name,
  s_street_type,
  s_suite_number,
  s_city,
  s_county,
  s_state,
  s_zip
LIMIT 100

Representative DataFrame

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

def q50_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(50)
    year = params.get("year", 2000)
    month = params.get("month", 9)
    col = ctx.col
    lit = ctx.lit
    lag = col("sr_returned_date_sk") - col("ss_sold_date_sk")
    return (
        ctx.get_table("store_sales")
        .join(
            ctx.get_table("store_returns"),
            left_on=["ss_ticket_number", "ss_item_sk", "ss_customer_sk"],
            right_on=["sr_ticket_number", "sr_item_sk", "sr_customer_sk"],
        )
        .join(ctx.get_table("store"), left_on="ss_store_sk", right_on="s_store_sk")
        .join(ctx.get_table("date_dim").select("d_date_sk"), left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(
            ctx.get_table("date_dim").filter((col("d_year") == lit(year)) & (col("d_moy") == lit(month))),
            left_on="sr_returned_date_sk",
            right_on="d_date_sk",
        )
        .group_by(*_Q50_STORE_COLS)
        .agg(
            ctx.when(lag <= 30).then(1).otherwise(0).sum().alias("30 days"),
            ctx.when((lag > 30) & (lag <= 60)).then(1).otherwise(0).sum().alias("31-60 days"),
            ctx.when((lag > 60) & (lag <= 90)).then(1).otherwise(0).sum().alias("61-90 days"),
            ctx.when((lag > 90) & (lag <= 120)).then(1).otherwise(0).sum().alias("91-120 days"),
            ctx.when(lag > 120).then(1).otherwise(0).sum().alias(">120 days"),
        )
        .sort(_Q50_STORE_COLS, nulls_last=True)
        .limit(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['50'])

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

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

Source

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