TPC-DS Q45 — Web Sales Customer Zip Analysis

TPC-DS benchmark · all TPC-DS queries

Reports web sales by customer zip code with OR filter on zip or item

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  ca_zip,
  ca_county,
  SUM(ws_sales_price)
FROM web_sales, customer, customer_address, date_dim, item
WHERE
  ws_bill_customer_sk = c_customer_sk
  AND c_current_addr_sk = ca_address_sk
  AND ws_item_sk = i_item_sk
  AND (
    SUBSTRING(ca_zip FROM 1 FOR 5) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792')
    OR i_item_id IN (
      SELECT
        i_item_id
      FROM item
      WHERE
        i_item_sk IN (2, 3, 5, 7, 11, 13, 17, 19, 23, 29)
    )
  )
  AND ws_sold_date_sk = d_date_sk
  AND d_qoy = 2
  AND d_year = 2000
GROUP BY
  ca_zip,
  ca_county
ORDER BY
  ca_zip,
  ca_county
LIMIT 100

Representative DataFrame

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

def q45_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(45)
    year = params.get("year", 2000)
    qoy = params.get("qoy", 2)
    group_column = params.get("gbobc", "ca_county")
    zip_codes = _Q45_ZIP_CODES
    item_sks = _Q45_ITEM_SKS

    web_sales, customer, customer_address, date_dim, item = _tables(
        ctx, "web_sales", "customer", "customer_address", "date_dim", "item"
    )
    col = ctx.col
    lit = ctx.lit

    item_ids_list = (
        item.filter(col("i_item_sk").is_in(item_sks)).select("i_item_id").unique().collect_column_as_list("i_item_id")
    )

    grouped = (
        web_sales.join(customer, left_on="ws_bill_customer_sk", right_on="c_customer_sk")
        .join(customer_address, left_on="c_current_addr_sk", right_on="ca_address_sk")
        .join(item, left_on="ws_item_sk", right_on="i_item_sk")
        .join(date_dim, left_on="ws_sold_date_sk", right_on="d_date_sk")
        .filter(
            (col("d_qoy") == lit(qoy))
            & (col("d_year") == lit(year))
            & (col("ca_zip").cast_string().str.slice(0, 5).is_in(zip_codes) | col("i_item_id").is_in(item_ids_list))
        )
        .group_by(col("ca_zip"), col(group_column))
        .agg(col("ws_sales_price").sum().alias("sum_ws_sales_price"))
    )
    return _sort_null_largest_expression(ctx, grouped, ["ca_zip", group_column], [False, False]).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['45'])

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

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

Source

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