TPC-DS-OBT Q64

TPC-DS-OBT benchmark · all TPC-DS-OBT queries

Representative SQL

Rendered for DataFusion with default parameters.

WITH cs_ui AS (
  SELECT
    item_sk AS cs_item_sk,
    SUM(ext_list_price) AS sale,
    SUM(refunded_cash + reversed_charge + store_credit) AS refund
  FROM tpcds_sales_returns_obt
  WHERE
    channel = 'catalog' AND has_return = 'Y'
  GROUP BY
    item_sk
  HAVING
    SUM(ext_list_price) > 2 * SUM(refunded_cash + reversed_charge + store_credit)
), cross_sales AS (
  SELECT
    o.item_i_product_name AS product_name,
    o.item_i_item_sk AS item_sk,
    o.store_s_store_name AS store_name,
    o.store_s_zip AS store_zip,
    o.bill_addr_ca_street_number AS b_street_number,
    o.bill_addr_ca_street_name AS b_street_name,
    o.bill_addr_ca_city AS b_city,
    o.bill_addr_ca_zip AS b_zip,
    o.bill_cur_addr_ca_street_number AS c_street_number,
    o.bill_cur_addr_ca_street_name AS c_street_name,
    o.bill_cur_addr_ca_city AS c_city,
    o.bill_cur_addr_ca_zip AS c_zip,
    o.sold_date_d_year AS syear,
    o.bill_first_sales_date_d_year AS fsyear,
    o.bill_first_shipto_date_d_year AS s2year,
    COUNT(*) AS cnt,
    SUM(o.wholesale_cost) AS s1,
    SUM(o.list_price) AS s2,
    SUM(o.coupon_amt) AS s3
  FROM tpcds_sales_returns_obt AS o
  JOIN cs_ui
    ON o.item_sk = cs_ui.cs_item_sk
  WHERE
    o.channel = 'store'
    AND o.has_return = 'Y'
    AND NOT o.store_s_store_sk IS NULL
    AND NOT o.sold_date_d_date_sk IS NULL
    AND NOT o.bill_customer_c_customer_sk IS NULL
    AND NOT o.bill_cdemo_cd_demo_sk IS NULL
    AND NOT o.bill_hdemo_hd_demo_sk IS NULL
    AND NOT o.bill_addr_ca_address_sk IS NULL
    AND NOT o.item_i_item_sk IS NULL
    AND NOT o.bill_cur_cdemo_cd_demo_sk IS NULL
    AND NOT o.bill_cur_hdemo_hd_demo_sk IS NULL
    AND NOT o.bill_cur_addr_ca_address_sk IS NULL
    AND NOT o.bill_first_sales_date_d_date_sk IS NULL
    AND NOT o.bill_first_shipto_date_d_date_sk IS NULL
    AND NOT o.promo_p_promo_sk IS NULL
    AND NOT o.bill_hdemo_ib_lower_bound IS NULL
    AND NOT o.bill_cur_hdemo_ib_lower_bound IS NULL
    AND o.bill_cdemo_cd_marital_status <> o.bill_cur_cdemo_cd_marital_status
    AND o.item_i_color IN ('maroon', 'burnished', 'dim', 'steel', 'navajo', 'chocolate')
    AND o.item_i_current_price BETWEEN 35 AND 35 + 10
    AND o.item_i_current_price BETWEEN 35 + 1 AND 35 + 15
  GROUP BY
    o.item_i_product_name,
    o.item_i_item_sk,
    o.store_s_store_name,
    o.store_s_zip,
    o.bill_addr_ca_street_number,
    o.bill_addr_ca_street_name,
    o.bill_addr_ca_city,
    o.bill_addr_ca_zip,
    o.bill_cur_addr_ca_street_number,
    o.bill_cur_addr_ca_street_name,
    o.bill_cur_addr_ca_city,
    o.bill_cur_addr_ca_zip,
    o.sold_date_d_year,
    o.bill_first_sales_date_d_year,
    o.bill_first_shipto_date_d_year
)
SELECT
  cs1.product_name,
  cs1.store_name,
  cs1.store_zip,
  cs1.b_street_number,
  cs1.b_street_name,
  cs1.b_city,
  cs1.b_zip,
  cs1.c_street_number,
  cs1.c_street_name,
  cs1.c_city,
  cs1.c_zip,
  cs1.syear,
  cs1.cnt,
  cs1.s1 AS s11,
  cs1.s2 AS s21,
  cs1.s3 AS s31,
  cs2.s1 AS s12,
  cs2.s2 AS s22,
  cs2.s3 AS s32,
  cs2.syear,
  cs2.cnt
FROM cross_sales AS cs1, cross_sales AS cs2
WHERE
  cs1.item_sk = cs2.item_sk
  AND cs1.syear = 2000
  AND cs2.syear = 2000 + 1
  AND cs2.cnt <= cs1.cnt
  AND cs1.store_name = cs2.store_name
  AND cs1.store_zip = cs2.store_zip
ORDER BY
  cs1.product_name,
  cs1.store_name,
  cs2.cnt,
  cs1.s1,
  cs2.s1

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

Source

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