TPC-DS-OBT Q61

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  promotions,
  total,
  CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100
FROM (
  SELECT
    SUM(obt.ext_sales_price) AS promotions
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.store_sk = obt.store_s_store_sk
      AND obt.promo_sk = obt.promo_p_promo_sk
      AND obt.ship_customer_sk = obt.bill_customer_c_customer_sk
      AND obt.bill_addr_ca_address_sk = obt.bill_customer_c_current_addr_sk
      AND obt.item_sk = obt.item_i_item_sk
      AND obt.bill_addr_ca_gmt_offset = -6
      AND obt.item_i_category = 'Books'
      AND (
        obt.promo_p_channel_dmail = 'Y'
        OR obt.promo_p_channel_email = 'Y'
        OR obt.promo_p_channel_tv = 'Y'
      )
      AND obt.store_s_gmt_offset = -6
      AND obt.sold_date_d_year = 1998
      AND obt.sold_date_d_moy = 11
    )
    AND channel = 'store'
) AS promotional_sales, (
  SELECT
    SUM(obt.ext_sales_price) AS total
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.store_sk = obt.store_s_store_sk
      AND obt.ship_customer_sk = obt.bill_customer_c_customer_sk
      AND obt.bill_addr_ca_address_sk = obt.bill_customer_c_current_addr_sk
      AND obt.item_sk = obt.item_i_item_sk
      AND obt.bill_addr_ca_gmt_offset = -6
      AND obt.item_i_category = 'Books'
      AND obt.store_s_gmt_offset = -6
      AND obt.sold_date_d_year = 1998
      AND obt.sold_date_d_moy = 11
    )
    AND channel = 'store'
) AS all_sales
ORDER BY
  promotions,
  total
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['61'])

Source

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