TPC-DS Q84 — Customer Income Band Lookup

TPC-DS benchmark · all TPC-DS queries

Customer lookup filtered by city and income band with store returns

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  c_customer_id AS customer_id,
  COALESCE(c_last_name, '') || ', ' || COALESCE(c_first_name, '') AS customername
FROM customer, customer_address, customer_demographics, household_demographics, income_band, store_returns
WHERE
  ca_city = 'Hopewell'
  AND c_current_addr_sk = ca_address_sk
  AND ib_lower_bound >= 32287
  AND ib_upper_bound <= 32287 + 50000
  AND ib_income_band_sk = hd_income_band_sk
  AND cd_demo_sk = c_current_cdemo_sk
  AND hd_demo_sk = c_current_hdemo_sk
  AND sr_cdemo_sk = cd_demo_sk
ORDER BY
  c_customer_id
LIMIT 100

Representative DataFrame

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

def q84_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(84)
    city = params.get("city", "Edgewood")
    income_min = params.get("income_band", 38128)
    income_max = income_min + 50000

    customer, customer_address, customer_demographics, household_demographics, income_band, store_returns = _tables(
        ctx,
        "customer",
        "customer_address",
        "customer_demographics",
        "household_demographics",
        "income_band",
        "store_returns",
    )

    col = ctx.col
    lit = ctx.lit

    ca_filtered = customer_address.filter(col("ca_city") == lit(city))

    ib_filtered = income_band.filter(
        (col("ib_lower_bound") >= lit(income_min)) & (col("ib_upper_bound") <= lit(income_max))
    )

    result = (
        customer.join(ca_filtered, left_on="c_current_addr_sk", right_on="ca_address_sk")
        .join(customer_demographics, left_on="c_current_cdemo_sk", right_on="cd_demo_sk")
        .join(household_demographics, left_on="c_current_hdemo_sk", right_on="hd_demo_sk")
        .join(ib_filtered, left_on="hd_income_band_sk", right_on="ib_income_band_sk")
        .join(store_returns.select("sr_cdemo_sk"), left_on="c_current_cdemo_sk", right_on="sr_cdemo_sk")
    )

    return (
        result.with_columns(
            (ctx.coalesce(col("c_last_name"), lit("")) + lit(", ") + ctx.coalesce(col("c_first_name"), lit(""))).alias(
                "customername"
            )
        )
        .select(
            [
                col("c_customer_id").alias("customer_id"),
                "customername",
            ]
        )
        .sort("customer_id")
        .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['84'])

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

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

Source

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