TPC-DS-OBT Q31

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH ss AS (
  SELECT
    obt.bill_addr_ca_county AS ca_county,
    obt.sold_date_d_qoy AS d_qoy,
    obt.sold_date_d_year AS d_year,
    SUM(obt.ext_sales_price) AS store_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.ship_addr_sk = obt.bill_addr_ca_address_sk
    )
    AND channel = 'store'
  GROUP BY
    ca_county,
    d_qoy,
    d_year
), ws AS (
  SELECT
    obt.bill_addr_ca_county AS ca_county,
    obt.sold_date_d_qoy AS d_qoy,
    obt.sold_date_d_year AS d_year,
    SUM(obt.ext_sales_price) AS web_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.bill_addr_sk = obt.bill_addr_ca_address_sk
    )
    AND channel = 'web'
  GROUP BY
    ca_county,
    d_qoy,
    d_year
)
SELECT
  ss1.ca_county,
  ss1.d_year,
  CAST(ws2.web_sales AS DOUBLE PRECISION) / ws1.web_sales AS web_q1_q2_increase,
  CAST(ss2.store_sales AS DOUBLE PRECISION) / ss1.store_sales AS store_q1_q2_increase,
  CAST(ws3.web_sales AS DOUBLE PRECISION) / ws2.web_sales AS web_q2_q3_increase,
  CAST(ss3.store_sales AS DOUBLE PRECISION) / ss2.store_sales AS store_q2_q3_increase
FROM ss AS ss1, ss AS ss2, ss AS ss3, ws AS ws1, ws AS ws2, ws AS ws3
WHERE
  ss1.d_qoy = 1
  AND ss1.d_year = 1998
  AND ss1.ca_county = ss2.ca_county
  AND ss2.d_qoy = 2
  AND ss2.d_year = 1998
  AND ss2.ca_county = ss3.ca_county
  AND ss3.d_qoy = 3
  AND ss3.d_year = 1998
  AND ss1.ca_county = ws1.ca_county
  AND ws1.d_qoy = 1
  AND ws1.d_year = 1998
  AND ws1.ca_county = ws2.ca_county
  AND ws2.d_qoy = 2
  AND ws2.d_year = 1998
  AND ws1.ca_county = ws3.ca_county
  AND ws3.d_qoy = 3
  AND ws3.d_year = 1998
  AND CASE
    WHEN ws1.web_sales > 0
    THEN CAST(ws2.web_sales AS DOUBLE PRECISION) / ws1.web_sales
    ELSE NULL
  END > CASE
    WHEN ss1.store_sales > 0
    THEN CAST(ss2.store_sales AS DOUBLE PRECISION) / ss1.store_sales
    ELSE NULL
  END
  AND CASE
    WHEN ws2.web_sales > 0
    THEN CAST(ws3.web_sales AS DOUBLE PRECISION) / ws2.web_sales
    ELSE NULL
  END > CASE
    WHEN ss2.store_sales > 0
    THEN CAST(ss3.store_sales AS DOUBLE PRECISION) / ss2.store_sales
    ELSE NULL
  END
ORDER BY
  ss1.ca_county

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

Source

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