TPC-DS Q8 — Store Sales Zip Code Net Profit
TPC-DS benchmark · all TPC-DS queries
Analyzes store net profit filtered by preferred customer zip codes
Representative SQL
Rendered for DataFusion with default parameters.
SELECT s_store_name, SUM(ss_net_profit)FROM store_sales, date_dim, store, ( SELECT ca_zip FROM ( SELECT SUBSTRING(ca_zip FROM 1 FOR 5) AS ca_zip FROM customer_address WHERE SUBSTRING(ca_zip FROM 1 FOR 5) IN ( '89436', '30868', '65085', '22977', '83927', '77557', '58429', '40697', '80614', '10502', '32779', '91137', '61265', '98294', '17921', '18427', '21203', '59362', '87291', '84093', '21505', '17184', '10866', '67898', '25797', '28055', '18377', '80332', '74535', '21757', '29742', '90885', '29898', '17819', '40811', '25990', '47513', '89531', '91068', '10391', '18846', '99223', '82637', '41368', '83658', '86199', '81625', '26696', '89338', '88425', '32200', '81427', '19053', '77471', '36610', '99823', '43276', '41249', '48584', '83550', '82276', '18842', '78890', '14090', '38123', '40936', '34425', '19850', '43286', '80072', '79188', '54191', '11395', '50497', '84861', '90733', '21068', '57666', '37119', '25004', '57835', '70067', '62878', '95806', '19303', '18840', '19124', '29785', '16737', '16022', '49613', '89977', '68310', '60069', '98360', '48649', '39050', '41793', '25002', '27413', '39736', '47208', '16515', '94808', '57648', '15009', '80015', '42961', '63982', '21744', '71853', '81087', '67468', '34175', '64008', '20261', '11201', '51799', '48043', '45645', '61163', '48375', '36447', '57042', '21218', '41100', '89951', '22745', '35851', '83326', '61125', '78298', '80752', '49858', '52940', '96976', '63792', '11376', '53582', '18717', '90226', '50530', '94203', '99447', '27670', '96577', '57856', '56372', '16165', '23427', '54561', '28806', '44439', '22926', '30123', '61451', '92397', '56979', '92309', '70873', '13355', '21801', '46346', '37562', '56458', '28286', '47306', '99555', '69399', '26234', '47546', '49661', '88601', '35943', '39936', '25632', '24611', '44166', '56648', '30379', '59785', '11110', '14329', '93815', '52226', '71381', '13842', '25612', '63294', '14664', '21077', '82626', '18799', '60915', '81020', '56447', '76619', '11433', '13414', '42548', '92713', '70467', '30884', '47484', '16072', '38936', '13036', '88376', '45539', '35901', '19506', '65690', '73957', '71850', '49231', '14276', '20005', '18384', '76615', '11635', '38177', '55607', '41369', '95447', '58581', '58149', '91946', '33790', '76232', '75692', '95464', '22246', '51061', '56692', '53121', '77209', '15482', '10688', '14868', '45907', '73520', '72666', '25734', '17959', '24677', '66446', '94627', '53535', '15560', '41967', '69297', '11929', '59403', '33283', '52232', '57350', '43933', '40921', '36635', '10827', '71286', '19736', '80619', '25251', '95042', '15526', '36496', '55854', '49124', '81980', '35375', '49157', '63512', '28944', '14946', '36503', '54010', '18767', '23969', '43905', '66979', '33113', '21286', '58471', '59080', '13395', '79144', '70373', '67031', '38360', '26705', '50906', '52406', '26066', '73146', '15884', '31897', '30045', '61068', '45550', '92454', '13376', '14354', '19770', '22928', '97790', '50723', '46081', '30202', '14410', '20223', '88500', '67298', '13261', '14172', '81410', '93578', '83583', '46047', '94167', '82564', '21156', '15799', '86709', '37931', '74703', '83103', '23054', '70470', '72008', '49247', '91911', '69998', '20961', '70070', '63197', '54853', '88191', '91830', '49521', '19454', '81450', '89091', '62378', '25683', '61869', '51744', '36580', '85778', '36871', '48121', '28810', '83712', '45486', '67393', '26935', '42393', '20132', '55349', '86057', '21309', '80218', '10094', '11357', '48819', '39734', '40758', '30432', '21204', '29467', '30214', '61024', '55307', '74621', '11622', '68908', '33032', '52868', '99194', '99900', '84936', '69036', '99149', '45013', '32895', '59004', '32322', '14933', '32936', '33562', '72550', '27385', '58049', '58200', '16808', '21360', '32961', '18586', '79307', '15492' ) INTERSECT SELECT ca_zip FROM ( SELECT SUBSTRING(ca_zip FROM 1 FOR 5) AS ca_zip, COUNT(*) AS cnt FROM customer_address, customer WHERE ca_address_sk = c_current_addr_sk AND c_preferred_cust_flag = 'Y' GROUP BY ca_zip HAVING COUNT(*) > 10 ) AS A1 ) AS A2) AS V1WHERE ss_store_sk = s_store_sk AND ss_sold_date_sk = d_date_sk AND d_qoy = 1 AND d_year = 2002 AND ( SUBSTRING(s_zip FROM 1 FOR 2) = SUBSTRING(V1.ca_zip FROM 1 FOR 2) )GROUP BY s_store_nameORDER BY s_store_nameLIMIT 100Representative DataFrame
Expression family (Polars, PySpark, DataFusion), default parameters.
def q8_expression_impl(ctx: DataFrameContext) -> Any: params = get_parameters(8) year = params.get("year", 1998) qoy = params.get("qoy") zip_codes = params.get("zip_codes")
store_sales, date_dim, store, customer_address, customer = _tables( ctx, "store_sales", "date_dim", "store", "customer_address", "customer" )
col = ctx.col lit = ctx.lit
date_filtered = date_dim.filter((col("d_qoy") == lit(qoy)) & (col("d_year") == lit(year)))
preferred_zips = ( customer_address.join( customer.filter(col("c_preferred_cust_flag") == lit("Y")), left_on="ca_address_sk", right_on="c_current_addr_sk", ) .with_columns(col("ca_zip").cast_string().str.slice(0, 5).alias("zip5")) .group_by("zip5") .agg(ctx.len().alias("cnt")) .filter(col("cnt") > lit(10)) .select("zip5") )
target_zips = customer_address.filter(col("ca_zip").cast_string().str.slice(0, 5).is_in(zip_codes)).select( col("ca_zip").cast_string().str.slice(0, 5).alias("zip5") )
valid_zips = target_zips.join(preferred_zips, on="zip5", how="semi")
result = ( store_sales.join(date_filtered, left_on="ss_sold_date_sk", right_on="d_date_sk") .join(store, left_on="ss_store_sk", right_on="s_store_sk") .with_columns(col("s_zip").cast_string().str.slice(0, 2).alias("store_zip2")) )
valid_zips_2char = valid_zips.with_columns(col("zip5").str.slice(0, 2).alias("zip2")).select("zip2").unique()
result = result.join(valid_zips_2char, left_on="store_zip2", right_on="zip2")
return _sort_null_largest_expression( ctx, result.group_by("s_store_name").agg(ctx.sum("ss_net_profit").alias("sum_net_profit")), ["s_store_name"] ).head(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 factorqueries = bench.get_queries(dialect="datafusion") # your platform's dialect; drop dialect= for native SQLprint(queries['8'])Parameter substitution for this benchmark is seed-driven; pass a seed for a specific stream’s parameters:
print(bench.get_query(8, 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', '8', family="expression").source) # or family="pandas"Source
Query text: _sources/tpc-ds/query_templates/query8.tpl