TPC-DS Q79 — Store Sales Customer/Store Profit

TPC-DS benchmark · all TPC-DS queries

Reports customer profit by store for specific household demographics

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  c_last_name,
  c_first_name,
  SUBSTRING(s_city FROM 1 FOR 30),
  ss_ticket_number,
  amt,
  profit
FROM (
  SELECT
    ss_ticket_number,
    ss_customer_sk,
    store.s_city,
    SUM(ss_coupon_amt) AS amt,
    SUM(ss_net_profit) AS profit
  FROM store_sales, date_dim, store, household_demographics
  WHERE
    store_sales.ss_sold_date_sk = date_dim.d_date_sk
    AND store_sales.ss_store_sk = store.s_store_sk
    AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
    AND (
      household_demographics.hd_dep_count = 8
      OR household_demographics.hd_vehicle_count > 0
    )
    AND date_dim.d_dow = 1
    AND date_dim.d_year IN (1998, 1998 + 1, 1998 + 2)
    AND store.s_number_employees BETWEEN 200 AND 295
  GROUP BY
    ss_ticket_number,
    ss_customer_sk,
    ss_addr_sk,
    store.s_city
) AS ms, customer
WHERE
  ss_customer_sk = c_customer_sk
ORDER BY
  c_last_name,
  c_first_name,
  SUBSTRING(s_city FROM 1 FOR 30),
  profit
LIMIT 100

Representative DataFrame

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

def q79_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(79)
    year = params.get("year", 1998)
    years = [year, year + 1, year + 2]
    dep_count = params.get("dep_count", 8)
    vehicle_count = params.get("vehicle_count", 0)
    col = ctx.col
    lit = ctx.lit
    inner = (
        ctx.get_table("store_sales")
        .join(ctx.get_table("date_dim"), left_on="ss_sold_date_sk", right_on="d_date_sk")
        .join(ctx.get_table("store"), left_on="ss_store_sk", right_on="s_store_sk")
        .join(ctx.get_table("household_demographics"), left_on="ss_hdemo_sk", right_on="hd_demo_sk")
        .filter(
            col("d_year").is_in(years)
            & (col("d_dow") == lit(1))
            & ((col("hd_dep_count") == lit(dep_count)) | (col("hd_vehicle_count") > lit(vehicle_count)))
            & col("s_number_employees").is_between(200, 295)
        )
        .group_by("ss_ticket_number", "ss_customer_sk", "ss_addr_sk", col("s_city").alias("s_city"))
        .agg(
            col("ss_coupon_amt").sum().alias("amt"),
            col("ss_coupon_amt").count().alias("n_amt"),
            col("ss_net_profit").sum().alias("profit"),
            col("ss_net_profit").count().alias("n_profit"),
        )
    )
    return (
        inner.join(ctx.get_table("customer"), left_on="ss_customer_sk", right_on="c_customer_sk")
        .select(
            "c_last_name",
            "c_first_name",
            col("s_city").cast_string().str.slice(0, 30).alias("s_city"),
            "ss_ticket_number",
            ctx.when(col("n_amt") > lit(0)).then(col("amt")).otherwise(lit(None)).alias("amt"),
            ctx.when(col("n_profit") > lit(0)).then(col("profit")).otherwise(lit(None)).alias("profit"),
        )
        .sort(["c_last_name", "c_first_name", "s_city", "profit"], 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['79'])

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

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

Source

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