TPC-DS Q35 — Customer Demographics Multi-Channel Quarterly

TPC-DS benchmark · all TPC-DS queries

Demographics with state for customers in store AND (web OR catalog)

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  ca_state,
  cd_gender,
  cd_marital_status,
  cd_dep_count,
  COUNT(*) AS cnt1,
  AVG(cd_dep_count) AS aggone1,
  MAX(cd_dep_count) AS aggtwo1,
  SUM(cd_dep_count) AS aggthree1,
  cd_dep_employed_count,
  COUNT(*) AS cnt2,
  AVG(cd_dep_employed_count) AS aggone2,
  MAX(cd_dep_employed_count) AS aggtwo2,
  SUM(cd_dep_employed_count) AS aggthree2,
  cd_dep_college_count,
  COUNT(*) AS cnt3,
  AVG(cd_dep_college_count) AS aggone3,
  MAX(cd_dep_college_count) AS aggtwo3,
  SUM(cd_dep_college_count) AS aggthree3
FROM customer AS c, customer_address AS ca, customer_demographics
WHERE
  c.c_current_addr_sk = ca.ca_address_sk
  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_qoy < 4
  )
  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 = 1999
        AND d_qoy < 4
    )
    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 = 1999
        AND d_qoy < 4
    )
  )
GROUP BY
  ca_state,
  cd_gender,
  cd_marital_status,
  cd_dep_count,
  cd_dep_employed_count,
  cd_dep_college_count
ORDER BY
  ca_state,
  cd_gender,
  cd_marital_status,
  cd_dep_count,
  cd_dep_employed_count,
  cd_dep_college_count
LIMIT 100

Representative DataFrame

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

def q35_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(35)
    year = params.get("year", 2002)
    aggone, aggtwo, aggthree = (_Q35_EXPRESSION_AGGREGATES[name] for name in _q35_aggregates(params))
    col = ctx.col
    lit = ctx.lit
    date_filtered = ctx.get_table("date_dim").filter((col("d_year") == lit(year)) & (col("d_qoy") < lit(4)))
    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")
        .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 = [
        "ca_state",
        "cd_gender",
        "cd_marital_status",
        "cd_dep_count",
        "cd_dep_employed_count",
        "cd_dep_college_count",
    ]
    return (
        base.group_by(group_cols)
        .agg(
            [
                ctx.count().alias("cnt1"),
                getattr(col("cd_dep_count"), aggone)().alias("aggone1"),
                getattr(col("cd_dep_count"), aggtwo)().alias("aggtwo1"),
                getattr(col("cd_dep_count"), aggthree)().alias("aggthree1"),
                getattr(col("cd_dep_employed_count"), aggone)().alias("aggone2"),
                getattr(col("cd_dep_employed_count"), aggtwo)().alias("aggtwo2"),
                getattr(col("cd_dep_employed_count"), aggthree)().alias("aggthree2"),
                getattr(col("cd_dep_college_count"), aggone)().alias("aggone3"),
                getattr(col("cd_dep_college_count"), aggtwo)().alias("aggtwo3"),
                getattr(col("cd_dep_college_count"), aggthree)().alias("aggthree3"),
            ]
        )
        .with_columns([col("cnt1").alias("cnt2"), col("cnt1").alias("cnt3")])
        .select(
            [
                "ca_state",
                "cd_gender",
                "cd_marital_status",
                "cd_dep_count",
                "cnt1",
                "aggone1",
                "aggtwo1",
                "aggthree1",
                "cd_dep_employed_count",
                "cnt2",
                "aggone2",
                "aggtwo2",
                "aggthree2",
                "cd_dep_college_count",
                "cnt3",
                "aggone3",
                "aggtwo3",
                "aggthree3",
            ]
        )
        .sort(group_cols, nulls_last=True)
        .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['35'])

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

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

Source

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