TPC-DS Q85 — Web Sales Returns Demographics

TPC-DS benchmark · all TPC-DS queries

Analyzes web returns by reason with customer demographics filtering

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUBSTRING(r_reason_desc FROM 1 FOR 20),
  AVG(ws_quantity),
  AVG(wr_refunded_cash),
  AVG(wr_fee)
FROM web_sales, web_returns, web_page, customer_demographics AS cd1, customer_demographics AS cd2, customer_address, date_dim, reason
WHERE
  ws_web_page_sk = wp_web_page_sk
  AND ws_item_sk = wr_item_sk
  AND ws_order_number = wr_order_number
  AND ws_sold_date_sk = d_date_sk
  AND d_year = 1998
  AND cd1.cd_demo_sk = wr_refunded_cdemo_sk
  AND cd2.cd_demo_sk = wr_returning_cdemo_sk
  AND ca_address_sk = wr_refunded_addr_sk
  AND r_reason_sk = wr_reason_sk
  AND (
    (
      cd1.cd_marital_status = 'M'
      AND cd1.cd_marital_status = cd2.cd_marital_status
      AND cd1.cd_education_status = '4 yr Degree'
      AND cd1.cd_education_status = cd2.cd_education_status
      AND ws_sales_price BETWEEN 100.00 AND 150.00
    )
    OR (
      cd1.cd_marital_status = 'D'
      AND cd1.cd_marital_status = cd2.cd_marital_status
      AND cd1.cd_education_status = 'Primary'
      AND cd1.cd_education_status = cd2.cd_education_status
      AND ws_sales_price BETWEEN 50.00 AND 100.00
    )
    OR (
      cd1.cd_marital_status = 'U'
      AND cd1.cd_marital_status = cd2.cd_marital_status
      AND cd1.cd_education_status = 'Advanced Degree'
      AND cd1.cd_education_status = cd2.cd_education_status
      AND ws_sales_price BETWEEN 150.00 AND 200.00
    )
  )
  AND (
    (
      ca_country = 'United States'
      AND ca_state IN ('KY', 'GA', 'NM')
      AND ws_net_profit BETWEEN 100 AND 200
    )
    OR (
      ca_country = 'United States'
      AND ca_state IN ('MT', 'OR', 'IN')
      AND ws_net_profit BETWEEN 150 AND 300
    )
    OR (
      ca_country = 'United States'
      AND ca_state IN ('WI', 'MO', 'WV')
      AND ws_net_profit BETWEEN 50 AND 250
    )
  )
GROUP BY
  r_reason_desc
ORDER BY
  SUBSTRING(r_reason_desc FROM 1 FOR 20),
  AVG(ws_quantity),
  AVG(wr_refunded_cash),
  AVG(wr_fee)
LIMIT 100

Representative DataFrame

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

def q85_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(85)
    year = params.get("year", 1998)
    marital, education, states = _q85_groups(params)

    col = ctx.col
    lit = ctx.lit

    web_sales, web_returns, web_page, customer_demographics, customer_address, date_dim, reason = _tables(
        ctx, "web_sales", "web_returns", "web_page", "customer_demographics", "customer_address", "date_dim", "reason"
    )

    date_filtered = date_dim.filter(col("d_year") == lit(year))

    ws_wr = web_sales.join(
        web_returns,
        left_on=["ws_item_sk", "ws_order_number"],
        right_on=["wr_item_sk", "wr_order_number"],
    )

    ws_wr = ws_wr.join(web_page, left_on="ws_web_page_sk", right_on="wp_web_page_sk")

    ws_wr = ws_wr.join(date_filtered, left_on="ws_sold_date_sk", right_on="d_date_sk")

    cd_columns = customer_demographics.columns
    cd1 = customer_demographics.rename({c: f"cd1_{c}" for c in cd_columns})
    ws_wr = ws_wr.join(cd1, left_on="wr_refunded_cdemo_sk", right_on="cd1_cd_demo_sk")

    cd2 = customer_demographics.rename({c: f"cd2_{c}" for c in cd_columns})
    ws_wr = ws_wr.join(cd2, left_on="wr_returning_cdemo_sk", right_on="cd2_cd_demo_sk")

    ws_wr = ws_wr.join(customer_address, left_on="wr_refunded_addr_sk", right_on="ca_address_sk")

    ws_wr = ws_wr.join(reason, left_on="wr_reason_sk", right_on="r_reason_sk")

    demo_filter = None
    for status, degree, (low, high) in zip(marital, education, _Q85_PRICE_BANDS):
        group = (
            (col("cd1_cd_marital_status") == lit(status))
            & (col("cd1_cd_marital_status") == col("cd2_cd_marital_status"))
            & (col("cd1_cd_education_status") == lit(degree))
            & (col("cd1_cd_education_status") == col("cd2_cd_education_status"))
            & col("ws_sales_price").is_between(low, high)
        )
        demo_filter = group if demo_filter is None else demo_filter | group
    state_filter = None
    for group_states, (low, high) in zip(states, _Q85_PROFIT_BANDS):
        group = col("ca_state").is_in(group_states) & col("ws_net_profit").is_between(low, high)
        state_filter = group if state_filter is None else state_filter | group
    country_filter = (col("ca_country") == lit("United States")) & state_filter

    filtered = ws_wr.filter(demo_filter & country_filter)

    return _sort_null_largest_expression(
        ctx,
        filtered.group_by("r_reason_desc")
        .agg(
            [
                ctx.mean("ws_quantity").alias("avg_quantity"),
                ctx.mean("wr_refunded_cash").alias("avg_refunded_cash"),
                ctx.mean("wr_fee").alias("avg_fee"),
            ]
        )
        .with_columns(col("r_reason_desc").str.slice(0, 20).alias("reason_desc_short"))
        .select(["reason_desc_short", "avg_quantity", "avg_refunded_cash", "avg_fee"]),
        ["reason_desc_short", "avg_quantity", "avg_refunded_cash", "avg_fee"],
    ).head(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['85'])

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

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

Source

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