TPC-DS Q10 — Customer Demographics Multi-Channel

TPC-DS benchmark · all TPC-DS queries

Demographics for customers in store AND (web OR 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,
  cd_dep_count,
  COUNT(*) AS cnt4,
  cd_dep_employed_count,
  COUNT(*) AS cnt5,
  cd_dep_college_count,
  COUNT(*) AS cnt6
FROM customer AS c, customer_address AS ca, customer_demographics
WHERE
  c.c_current_addr_sk = ca.ca_address_sk
  AND ca_county IN (
    'Walker County',
    'Richland County',
    'Gaines County',
    'Douglas County',
    'Dona Ana County'
  )
  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 = 2002
      AND d_moy BETWEEN 4 AND 4 + 3
  )
  AND (
    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 = 2002
        AND d_moy BETWEEN 4 AND 4 + 3
    )
    OR 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 = 2002
        AND d_moy BETWEEN 4 AND 4 + 3
    )
  )
GROUP BY
  cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating,
  cd_dep_count,
  cd_dep_employed_count,
  cd_dep_college_count
ORDER BY
  cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating,
  cd_dep_count,
  cd_dep_employed_count,
  cd_dep_college_count
LIMIT 100

Representative DataFrame

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

def q10_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(10)
    year = params.get("year", 2002)
    month = params.get("month", 2)
    counties = params.get("counties")
    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 + 3))
    )
    customers = _store_and_other_channel_customers_expression(ctx, date_filtered)
    base = (
        ctx.get_table("customer")
        .join(ctx.get_table("customer_address"), left_on="c_current_addr_sk", right_on="ca_address_sk")
        .filter(col("ca_county").is_in(counties))
        .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")
    )
    group_cols = [
        "cd_gender",
        "cd_marital_status",
        "cd_education_status",
        "cd_purchase_estimate",
        "cd_credit_rating",
        "cd_dep_count",
        "cd_dep_employed_count",
        "cd_dep_college_count",
    ]
    select_cols = [
        "cd_gender",
        "cd_marital_status",
        "cd_education_status",
        "cnt1",
        "cd_purchase_estimate",
        "cnt2",
        "cd_credit_rating",
        "cnt3",
        "cd_dep_count",
        "cnt4",
        "cd_dep_employed_count",
        "cnt5",
        "cd_dep_college_count",
        "cnt6",
    ]
    return _sort_null_largest_expression(
        ctx,
        base.group_by(group_cols)
        .agg(ctx.count().alias("cnt"))
        .with_columns([col("cnt").alias(f"cnt{i}") for i in range(1, 7)])
        .select(select_cols),
        group_cols,
    ).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['10'])

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

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

Source

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