TPC-DS Q48 — Store Sales Quantity Demographics

TPC-DS benchmark · all TPC-DS queries

Computes sum of store sales quantity filtered by demographics

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  SUM(ss_quantity)
FROM store_sales, store, customer_demographics, customer_address, date_dim
WHERE
  s_store_sk = ss_store_sk
  AND ss_sold_date_sk = d_date_sk
  AND d_year = 1998
  AND (
    (
      cd_demo_sk = ss_cdemo_sk
      AND cd_marital_status = 'M'
      AND cd_education_status = '4 yr Degree'
      AND ss_sales_price BETWEEN 100.00 AND 150.00
    )
    OR (
      cd_demo_sk = ss_cdemo_sk
      AND cd_marital_status = 'D'
      AND cd_education_status = 'Primary'
      AND ss_sales_price BETWEEN 50.00 AND 100.00
    )
    OR (
      cd_demo_sk = ss_cdemo_sk
      AND cd_marital_status = 'U'
      AND cd_education_status = 'Advanced Degree'
      AND ss_sales_price BETWEEN 150.00 AND 200.00
    )
  )
  AND (
    (
      ss_addr_sk = ca_address_sk
      AND ca_country = 'United States'
      AND ca_state IN ('KY', 'GA', 'NM')
      AND ss_net_profit BETWEEN 0 AND 2000
    )
    OR (
      ss_addr_sk = ca_address_sk
      AND ca_country = 'United States'
      AND ca_state IN ('MT', 'OR', 'IN')
      AND ss_net_profit BETWEEN 150 AND 3000
    )
    OR (
      ss_addr_sk = ca_address_sk
      AND ca_country = 'United States'
      AND ca_state IN ('WI', 'MO', 'WV')
      AND ss_net_profit BETWEEN 50 AND 25000
    )
  )

Representative DataFrame

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

def q48_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(48)
    year = params.get("year", 1998)
    demo1_marital = params.get("demo1_marital", "M")
    demo1_education = params.get("demo1_education", "4 yr Degree")
    demo2_marital = params.get("demo2_marital", "D")
    demo2_education = params.get("demo2_education", "Primary")
    demo3_marital = params.get("demo3_marital", "U")
    demo3_education = params.get("demo3_education", "Advanced Degree")
    states1 = params.get("states1", ["KY", "GA", "NM"])
    states2 = params.get("states2", ["MT", "OR", "IN"])
    states3 = params.get("states3", ["WI", "MO", "WV"])

    store_sales, store, customer_demographics, customer_address, date_dim = _tables(
        ctx, "store_sales", "store", "customer_demographics", "customer_address", "date_dim"
    )
    col = ctx.col
    lit = ctx.lit

    return (
        store_sales.join(store, left_on="ss_store_sk", right_on="s_store_sk")
        .join(customer_demographics, left_on="ss_cdemo_sk", right_on="cd_demo_sk")
        .join(customer_address, left_on="ss_addr_sk", right_on="ca_address_sk")
        .join(date_dim, left_on="ss_sold_date_sk", right_on="d_date_sk")
        .filter(
            (col("d_year") == lit(year))
            & (
                (
                    (col("cd_marital_status") == lit(demo1_marital))
                    & (col("cd_education_status") == lit(demo1_education))
                    & col("ss_sales_price").is_between(100.0, 150.0)
                )
                | (
                    (col("cd_marital_status") == lit(demo2_marital))
                    & (col("cd_education_status") == lit(demo2_education))
                    & col("ss_sales_price").is_between(50.0, 100.0)
                )
                | (
                    (col("cd_marital_status") == lit(demo3_marital))
                    & (col("cd_education_status") == lit(demo3_education))
                    & col("ss_sales_price").is_between(150.0, 200.0)
                )
            )
            & (
                (
                    (col("ca_country") == lit("United States"))
                    & col("ca_state").is_in(states1)
                    & col("ss_net_profit").is_between(0, 2000)
                )
                | (
                    (col("ca_country") == lit("United States"))
                    & col("ca_state").is_in(states2)
                    & col("ss_net_profit").is_between(150, 3000)
                )
                | (
                    (col("ca_country") == lit("United States"))
                    & col("ca_state").is_in(states3)
                    & col("ss_net_profit").is_between(50, 25000)
                )
            )
        )
        .select(col("ss_quantity").sum().alias("sum_ss_quantity"))
    )

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['48'])

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

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

Source

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