TPC-DS Q96 — Store Sales Time Count

TPC-DS benchmark · all TPC-DS queries

Counts store sales for specific time periods

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  COUNT(*)
FROM store_sales, household_demographics, time_dim, store
WHERE
  ss_sold_time_sk = time_dim.t_time_sk
  AND ss_hdemo_sk = household_demographics.hd_demo_sk
  AND ss_store_sk = s_store_sk
  AND time_dim.t_hour = 8
  AND time_dim.t_minute >= 30
  AND household_demographics.hd_dep_count = 5
  AND store.s_store_name = 'ese'
ORDER BY
  COUNT(*)
LIMIT 100

Representative DataFrame

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

def q96_expression_impl(ctx: DataFrameContext) -> Any:
    time_dim, store_sales, store, household_demographics = _tables(
        ctx, "time_dim", "store_sales", "store", "household_demographics"
    )
    col = ctx.col
    lit = ctx.lit

    params = get_parameters(96)
    t_hour = int(params.get("hour", 8))
    dep_count = int(params.get("dep_count", 5))
    store_name = _Q96_STORE_NAME

    return (
        store_sales.join(time_dim, left_on="ss_sold_time_sk", right_on="t_time_sk")
        .join(store, left_on="ss_store_sk", right_on="s_store_sk")
        .join(household_demographics, left_on="ss_hdemo_sk", right_on="hd_demo_sk")
        .filter(
            (col("t_hour") == lit(t_hour))
            & (col("t_minute") >= lit(30))
            & (col("hd_dep_count") == lit(dep_count))
            & (col("s_store_name") == lit(store_name))
        )
        .select(col("ss_sold_time_sk").count().alias("count"))
    )

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

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

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

Source

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