TPC-DS Q31 — Store/Web Quarterly Sales Growth

TPC-DS benchmark · all TPC-DS queries

Compares Q1->Q2 and Q2->Q3 growth between store and web channels by county

Representative SQL

Rendered for DataFusion with default parameters.

WITH ss AS (
  SELECT
    ca_county,
    d_qoy,
    d_year,
    SUM(ss_ext_sales_price) AS store_sales
  FROM store_sales, date_dim, customer_address
  WHERE
    ss_sold_date_sk = d_date_sk AND ss_addr_sk = ca_address_sk
  GROUP BY
    ca_county,
    d_qoy,
    d_year
), ws AS (
  SELECT
    ca_county,
    d_qoy,
    d_year,
    SUM(ws_ext_sales_price) AS web_sales
  FROM web_sales, date_dim, customer_address
  WHERE
    ws_sold_date_sk = d_date_sk AND ws_bill_addr_sk = ca_address_sk
  GROUP BY
    ca_county,
    d_qoy,
    d_year
)
SELECT
  ss1.ca_county,
  ss1.d_year,
  ws2.web_sales / ws1.web_sales AS web_q1_q2_increase,
  ss2.store_sales / ss1.store_sales AS store_q1_q2_increase,
  ws3.web_sales / ws2.web_sales AS web_q2_q3_increase,
  ss3.store_sales / ss2.store_sales AS store_q2_q3_increase
FROM ss AS ss1, ss AS ss2, ss AS ss3, ws AS ws1, ws AS ws2, ws AS ws3
WHERE
  ss1.d_qoy = 1
  AND ss1.d_year = 2000
  AND ss1.ca_county = ss2.ca_county
  AND ss2.d_qoy = 2
  AND ss2.d_year = 2000
  AND ss2.ca_county = ss3.ca_county
  AND ss3.d_qoy = 3
  AND ss3.d_year = 2000
  AND ss1.ca_county = ws1.ca_county
  AND ws1.d_qoy = 1
  AND ws1.d_year = 2000
  AND ws1.ca_county = ws2.ca_county
  AND ws2.d_qoy = 2
  AND ws2.d_year = 2000
  AND ws1.ca_county = ws3.ca_county
  AND ws3.d_qoy = 3
  AND ws3.d_year = 2000
  AND CASE WHEN ws1.web_sales > 0 THEN ws2.web_sales / ws1.web_sales ELSE NULL END > CASE WHEN ss1.store_sales > 0 THEN ss2.store_sales / ss1.store_sales ELSE NULL END
  AND CASE WHEN ws2.web_sales > 0 THEN ws3.web_sales / ws2.web_sales ELSE NULL END > CASE WHEN ss2.store_sales > 0 THEN ss3.store_sales / ss2.store_sales ELSE NULL END
ORDER BY
  ss1.d_year

Representative DataFrame

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

def q31_expression_impl(ctx: DataFrameContext) -> Any:
    col = ctx.col
    lit = ctx.lit

    params = get_parameters(31)
    year = params.get("year", 2000)
    order_by = _q31_order_column(params.get("order_by", "ss1.ca_county"))

    store_sales, web_sales, date_dim, customer_address = _tables(
        ctx, "store_sales", "web_sales", "date_dim", "customer_address"
    )

    ss = (
        store_sales.join(date_dim, left_on="ss_sold_date_sk", right_on="d_date_sk", how="inner")
        .join(customer_address, left_on="ss_addr_sk", right_on="ca_address_sk", how="inner")
        .group_by(["ca_county", "d_qoy", "d_year"])
        .agg(col("ss_ext_sales_price").sum().alias("store_sales"))
    )

    ws = (
        web_sales.join(date_dim, left_on="ws_sold_date_sk", right_on="d_date_sk", how="inner")
        .join(customer_address, left_on="ws_bill_addr_sk", right_on="ca_address_sk", how="inner")
        .group_by(["ca_county", "d_qoy", "d_year"])
        .agg(col("ws_ext_sales_price").sum().alias("web_sales"))
    )

    ss1 = ss.filter((col("d_qoy") == 1) & (col("d_year") == year))
    ss2 = ss.filter((col("d_qoy") == 2) & (col("d_year") == year))
    ss3 = ss.filter((col("d_qoy") == 3) & (col("d_year") == year))

    ws1 = ws.filter((col("d_qoy") == 1) & (col("d_year") == year))
    ws2 = ws.filter((col("d_qoy") == 2) & (col("d_year") == year))
    ws3 = ws.filter((col("d_qoy") == 3) & (col("d_year") == year))

    result = (
        ss1.select([col("ca_county"), col("store_sales").alias("ss1_sales")])
        .join(
            ss2.select([col("ca_county"), col("store_sales").alias("ss2_sales")]),
            on="ca_county",
            how="inner",
        )
        .join(
            ss3.select([col("ca_county"), col("store_sales").alias("ss3_sales")]),
            on="ca_county",
            how="inner",
        )
        .join(
            ws1.select([col("ca_county"), col("web_sales").alias("ws1_sales")]),
            on="ca_county",
            how="inner",
        )
        .join(
            ws2.select([col("ca_county"), col("web_sales").alias("ws2_sales")]),
            on="ca_county",
            how="inner",
        )
        .join(
            ws3.select([col("ca_county"), col("web_sales").alias("ws3_sales")]),
            on="ca_county",
            how="inner",
        )
    )

    result = result.with_columns(
        [
            lit(year).alias("d_year"),
            (col("ws2_sales") / col("ws1_sales")).alias("web_q1_q2_increase"),
            (col("ss2_sales") / col("ss1_sales")).alias("store_q1_q2_increase"),
            (col("ws3_sales") / col("ws2_sales")).alias("web_q2_q3_increase"),
            (col("ss3_sales") / col("ss2_sales")).alias("store_q2_q3_increase"),
        ]
    )

    result = result.filter(
        (
            ctx.when(col("ws1_sales") > 0).then(col("ws2_sales") / col("ws1_sales")).otherwise(None)
            > ctx.when(col("ss1_sales") > 0).then(col("ss2_sales") / col("ss1_sales")).otherwise(None)
        )
        & (
            ctx.when(col("ws2_sales") > 0).then(col("ws3_sales") / col("ws2_sales")).otherwise(None)
            > ctx.when(col("ss2_sales") > 0).then(col("ss3_sales") / col("ss2_sales")).otherwise(None)
        )
    )

    return result.select(
        [
            col("ca_county"),
            col("d_year"),
            col("web_q1_q2_increase"),
            col("store_q1_q2_increase"),
            col("web_q2_q3_increase"),
            col("store_q2_q3_increase"),
        ]
    ).sort(order_by)

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

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

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

Source

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