Skip to content

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_return
FROM customer_total_return AS ctr1
JOIN cust
ON ctr1.ctr_customer_sk = cust.c_customer_sk
WHERE
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_return
LIMIT 100

Representative 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 factor
queries = bench.get_queries(dialect="datafusion") # your platform's dialect; drop dialect= for native SQL
print(queries['81'])

Source

Query text: benchbox/core/tpcds_obt/queries.py