TPC-DS-OBT Q79

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  obt.bill_customer_c_last_name,
  obt.bill_customer_c_first_name,
  SUBSTRING(obt.store_s_city FROM 1 FOR 30),
  obt.sale_id,
  amt,
  profit
FROM tpcds_sales_returns_obt AS obt
CROSS JOIN (
  SELECT
    sub_obt.sale_id AS ms_sale_id,
    sub_obt.ship_customer_sk AS ms_ship_customer_sk,
    store_s_city,
    SUM(sub_obt.coupon_amt) AS amt,
    SUM(sub_obt.net_profit) AS profit
  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 (
        bill_hdemo_hd_dep_count = 0 OR bill_hdemo_hd_vehicle_count > -1
      )
      AND sold_date_d_dow = 1
      AND sold_date_d_year IN (1998, 1998 + 1, 1998 + 2)
      AND store_s_number_employees BETWEEN 200 AND 295
    )
    AND channel = 'store'
  GROUP BY
    sub_obt.sale_id,
    sub_obt.ship_customer_sk,
    sub_obt.ship_addr_sk,
    store_s_city
) AS ms
WHERE
  obt.sale_id = ms.ms_sale_id
  AND obt.ship_customer_sk = ms.ms_ship_customer_sk
  AND obt.ship_customer_sk = obt.bill_customer_c_customer_sk
  AND channel = 'store'
ORDER BY
  obt.bill_customer_c_last_name,
  obt.bill_customer_c_first_name,
  SUBSTRING(obt.store_s_city FROM 1 FOR 30),
  profit
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['79'])

Source

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