TPC-DS-OBT Q81
TPC-DS-OBT benchmark · all TPC-DS-OBT queries
Representative SQL
Rendered for DataFusion with default parameters.
WITH customer_total_return AS ( SELECT returning_customer_c_customer_sk AS ctr_customer_sk, returning_addr_ca_state AS ctr_state, SUM(return_amount_inc_tax) AS ctr_total_return FROM tpcds_sales_returns_obt WHERE channel = 'catalog' AND has_return = 'Y' AND return_date_d_year = 1998 AND NOT returning_addr_ca_address_sk IS NULL GROUP BY returning_customer_c_customer_sk, returning_addr_ca_state), cust AS ( SELECT DISTINCT returning_customer_c_customer_sk AS c_customer_sk, returning_customer_c_customer_id AS c_customer_id, returning_customer_c_salutation AS c_salutation, returning_customer_c_first_name AS c_first_name, returning_customer_c_last_name AS c_last_name, returning_cur_addr_ca_street_number AS ca_street_number, returning_cur_addr_ca_street_name AS ca_street_name, returning_cur_addr_ca_street_type AS ca_street_type, returning_cur_addr_ca_suite_number AS ca_suite_number, returning_cur_addr_ca_city AS ca_city, returning_cur_addr_ca_county AS ca_county, returning_cur_addr_ca_state AS ca_state, returning_cur_addr_ca_zip AS ca_zip, returning_cur_addr_ca_country AS ca_country, returning_cur_addr_ca_gmt_offset AS ca_gmt_offset, returning_cur_addr_ca_location_type AS ca_location_type FROM tpcds_sales_returns_obt WHERE channel = 'catalog' AND has_return = 'Y' AND NOT returning_customer_c_customer_sk IS NULL)SELECT c_customer_id, c_salutation, c_first_name, c_last_name, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type, ctr_total_returnFROM customer_total_return AS ctr1JOIN cust ON ctr1.ctr_customer_sk = cust.c_customer_skWHERE ctr1.ctr_total_return > ( SELECT AVG(ctr_total_return) * 1.2 FROM customer_total_return AS ctr2 WHERE ctr1.ctr_state = ctr2.ctr_state ) AND cust.ca_state = 'IL'ORDER BY c_customer_id, c_salutation, c_first_name, c_last_name, ca_street_number, ca_street_name, ca_street_type, ca_suite_number, ca_city, ca_county, ca_state, ca_zip, ca_country, ca_gmt_offset, ca_location_type, ctr_total_returnLIMIT 100Representative DataFrame
No DataFrame implementation is registered for this query.
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 TPCDSOBT
bench = TPCDSOBT(scale_factor=1.0) # your scale factorqueries = bench.get_queries(dialect="datafusion") # your platform's dialect; drop dialect= for native SQLprint(queries['81'])Source
Query text: benchbox/core/tpcds_obt/queries.py