TPC-DS-OBT Q73

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,
  dj.sale_id,
  cnt
FROM tpcds_sales_returns_obt AS obt
CROSS JOIN (
  SELECT
    obt.sale_id,
    obt.ship_customer_sk,
    COUNT(*) AS cnt
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = sold_date_d_date_sk
      AND obt.store_sk = store_s_store_sk
      AND obt.ship_hdemo_sk = bill_hdemo_hd_demo_sk
      AND sold_date_d_dom BETWEEN 1 AND 2
      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
      AND sold_date_d_year IN (1998, 1998 + 1, 1998 + 2)
      AND store_s_county IN ('0', '0', '0', '0')
    )
    AND channel = 'store'
  GROUP BY
    obt.sale_id,
    obt.ship_customer_sk
) AS dj
WHERE
  (
    dj.ship_customer_sk = obt.bill_customer_c_customer_sk AND cnt BETWEEN 1 AND 5
  )
  AND channel = 'store'
ORDER BY
  cnt DESC,
  bill_customer_c_last_name ASC

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

Source

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