TPC-DS Q69 — Customer Demographics Store-Only

TPC-DS benchmark · all TPC-DS queries

Demographics for customers who only shop in store (NOT web/catalog)

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  cd_gender,
  cd_marital_status,
  cd_education_status,
  COUNT(*) AS cnt1,
  cd_purchase_estimate,
  COUNT(*) AS cnt2,
  cd_credit_rating,
  COUNT(*) AS cnt3
FROM customer AS c, customer_address AS ca, customer_demographics
WHERE
  c.c_current_addr_sk = ca.ca_address_sk
  AND ca_state IN ('CO', 'IL', 'MN')
  AND cd_demo_sk = c.c_current_cdemo_sk
  AND EXISTS(
    SELECT
      *
    FROM store_sales, date_dim
    WHERE
      c.c_customer_sk = ss_customer_sk
      AND ss_sold_date_sk = d_date_sk
      AND d_year = 1999
      AND d_moy BETWEEN 1 AND 1 + 2
  )
  AND (
    NOT EXISTS(
      SELECT
        *
      FROM web_sales, date_dim
      WHERE
        c.c_customer_sk = ws_bill_customer_sk
        AND ws_sold_date_sk = d_date_sk
        AND d_year = 1999
        AND d_moy BETWEEN 1 AND 1 + 2
    )
    AND NOT EXISTS(
      SELECT
        *
      FROM catalog_sales, date_dim
      WHERE
        c.c_customer_sk = cs_ship_customer_sk
        AND cs_sold_date_sk = d_date_sk
        AND d_year = 1999
        AND d_moy BETWEEN 1 AND 1 + 2
    )
  )
GROUP BY
  cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating
ORDER BY
  cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating
LIMIT 100

Representative DataFrame

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

def q69_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(69)
    year = params.get("year", 2001)
    month = params.get("month", 4)
    states = params.get("states", ["KY", "GA", "NM"])
    col = ctx.col
    lit = ctx.lit
    date_filtered = ctx.get_table("date_dim").filter(
        (col("d_year") == lit(year)) & (col("d_moy") >= lit(month)) & (col("d_moy") <= lit(month + 2))
    )
    customers = _store_only_channel_customers_expression(ctx, date_filtered)
    group_cols = ["cd_gender", "cd_marital_status", "cd_education_status", "cd_purchase_estimate", "cd_credit_rating"]
    return _sort_null_largest_expression(
        ctx,
        ctx.get_table("customer")
        .join(
            ctx.get_table("customer_address").filter(col("ca_state").is_in(states)),
            left_on="c_current_addr_sk",
            right_on="ca_address_sk",
        )
        .join(ctx.get_table("customer_demographics"), left_on="c_current_cdemo_sk", right_on="cd_demo_sk")
        .join(customers, left_on="c_customer_sk", right_on="customer_sk", how="semi")
        .group_by(group_cols)
        .agg(ctx.count().alias("cnt1"))
        .with_columns([col("cnt1").alias("cnt2"), col("cnt1").alias("cnt3")])
        .select(
            "cd_gender",
            "cd_marital_status",
            "cd_education_status",
            "cnt1",
            "cd_purchase_estimate",
            "cnt2",
            "cd_credit_rating",
            "cnt3",
        ),
        group_cols,
    ).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['69'])

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

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

Source

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