TPC-DS Q43 — Store Sales Day Analysis

TPC-DS benchmark · all TPC-DS queries

Reports store sales by day of week for a specific year

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  s_store_name,
  s_store_id,
  SUM(CASE WHEN (
    d_day_name = 'Sunday'
  ) THEN ss_sales_price ELSE NULL END) AS sun_sales,
  SUM(CASE WHEN (
    d_day_name = 'Monday'
  ) THEN ss_sales_price ELSE NULL END) AS mon_sales,
  SUM(CASE WHEN (
    d_day_name = 'Tuesday'
  ) THEN ss_sales_price ELSE NULL END) AS tue_sales,
  SUM(CASE WHEN (
    d_day_name = 'Wednesday'
  ) THEN ss_sales_price ELSE NULL END) AS wed_sales,
  SUM(CASE WHEN (
    d_day_name = 'Thursday'
  ) THEN ss_sales_price ELSE NULL END) AS thu_sales,
  SUM(CASE WHEN (
    d_day_name = 'Friday'
  ) THEN ss_sales_price ELSE NULL END) AS fri_sales,
  SUM(CASE WHEN (
    d_day_name = 'Saturday'
  ) THEN ss_sales_price ELSE NULL END) AS sat_sales
FROM date_dim, store_sales, store
WHERE
  d_date_sk = ss_sold_date_sk
  AND s_store_sk = ss_store_sk
  AND s_gmt_offset = -5
  AND d_year = 1998
GROUP BY
  s_store_name,
  s_store_id
ORDER BY
  s_store_name,
  s_store_id,
  sun_sales,
  mon_sales,
  tue_sales,
  wed_sales,
  thu_sales,
  fri_sales,
  sat_sales
LIMIT 100

Representative DataFrame

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

def q43_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters(43)
    gmt_offset = params.get("gmt_offset", -5.0)
    year = params.get("year", 1998)
    col = ctx.col
    lit = ctx.lit
    days = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"]
    aliases = ["sun_sales", "mon_sales", "tue_sales", "wed_sales", "thu_sales", "fri_sales", "sat_sales"]
    return _sort_null_largest_expression(
        ctx,
        ctx.get_table("date_dim")
        .join(ctx.get_table("store_sales"), left_on="d_date_sk", right_on="ss_sold_date_sk")
        .join(ctx.get_table("store"), left_on="ss_store_sk", right_on="s_store_sk")
        .filter((col("s_gmt_offset") == lit(gmt_offset)) & (col("d_year") == lit(year)))
        .group_by("s_store_name", "s_store_id")
        .agg(
            *(
                _sum_or_null_expression(
                    ctx, ctx.when(col("d_day_name") == lit(day)).then(col("ss_sales_price")).otherwise(lit(None))
                ).alias(alias)
                for day, alias in zip(days, aliases)
            )
        ),
        ["s_store_name", "s_store_id", *aliases],
    ).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['43'])

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

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

Source

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