TPC-DS-OBT Q34

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  bill_customer_c_last_name,
  bill_customer_c_first_name,
  bill_customer_c_salutation,
  bill_customer_c_preferred_cust_flag,
  sale_id,
  cnt
FROM tpcds_sales_returns_obt AS obt
CROSS JOIN (
  SELECT
    sub_obt.sale_id AS dn_sale_id,
    sub_obt.ship_customer_sk AS dn_ship_customer_sk,
    COUNT(*) AS cnt
  FROM tpcds_sales_returns_obt AS sub_obt
  WHERE
    (
      sub_obt.sold_date_sk = sold_date_d_date_sk
      AND sub_obt.store_sk = store_s_store_sk
      AND sub_obt.ship_hdemo_sk = bill_hdemo_hd_demo_sk
      AND (
        sold_date_d_dom BETWEEN 1 AND 3 OR sold_date_d_dom BETWEEN 25 AND 28
      )
      AND (
        bill_hdemo_hd_buy_potential = '1001-5000'
        OR bill_hdemo_hd_buy_potential = '0-500'
      )
      AND bill_hdemo_hd_vehicle_count > 0
      AND (
        CASE
          WHEN bill_hdemo_hd_vehicle_count > 0
          THEN CAST(bill_hdemo_hd_dep_count AS DOUBLE PRECISION) / bill_hdemo_hd_vehicle_count
          ELSE NULL
        END
      ) > 1.2
      AND sold_date_d_year IN (1998, 1998 + 1, 1998 + 2)
      AND store_s_county IN ('0', '0', '0', '0', '0', '0', '0', '0')
    )
    AND channel = 'store'
  GROUP BY
    sub_obt.sale_id,
    sub_obt.ship_customer_sk
) AS dn
WHERE
  obt.sale_id = dn.dn_sale_id
  AND obt.ship_customer_sk = dn.dn_ship_customer_sk
  AND (
    ship_customer_sk = bill_customer_c_customer_sk AND cnt BETWEEN 15 AND 20
  )
  AND channel = 'store'
ORDER BY
  bill_customer_c_last_name,
  bill_customer_c_first_name,
  bill_customer_c_salutation,
  bill_customer_c_preferred_cust_flag DESC,
  sale_id

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

Source

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