TPC-DS-OBT Q51

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH web_v1 AS (
  SELECT
    obt.item_sk AS item_sk,
    obt.sold_date_d_date AS d_date,
    SUM(SUM(obt.sales_price)) OVER (
      PARTITION BY obt.item_sk
      ORDER BY obt.sold_date_d_date
      rows BETWEEN UNBOUNDED preceding AND CURRENT ROW
    ) AS cume_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.sold_date_d_month_seq BETWEEN 1176 AND 1176 + 11
      AND NOT obt.item_sk IS NULL
    )
    AND channel = 'web'
  GROUP BY
    obt.item_sk,
    obt.sold_date_d_date
), store_v1 AS (
  SELECT
    obt.item_sk AS item_sk,
    obt.sold_date_d_date AS d_date,
    SUM(SUM(obt.sales_price)) OVER (
      PARTITION BY obt.item_sk
      ORDER BY obt.sold_date_d_date
      rows BETWEEN UNBOUNDED preceding AND CURRENT ROW
    ) AS cume_sales
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.sold_date_sk = obt.sold_date_d_date_sk
      AND obt.sold_date_d_month_seq BETWEEN 1176 AND 1176 + 11
      AND NOT obt.item_sk IS NULL
    )
    AND channel = 'store'
  GROUP BY
    obt.item_sk,
    obt.sold_date_d_date
)
SELECT
  *
FROM (
  SELECT
    item_sk,
    d_date,
    web_sales,
    store_sales,
    MAX(web_sales) OVER (
      PARTITION BY item_sk
      ORDER BY d_date
      rows BETWEEN UNBOUNDED preceding AND CURRENT ROW
    ) AS web_cumulative,
    MAX(store_sales) OVER (
      PARTITION BY item_sk
      ORDER BY d_date
      rows BETWEEN UNBOUNDED preceding AND CURRENT ROW
    ) AS store_cumulative
  FROM (
    SELECT
      COALESCE(web.item_sk, store.item_sk) AS item_sk,
      CASE WHEN NOT web.d_date IS NULL THEN web.d_date ELSE store.d_date END AS d_date,
      web.cume_sales AS web_sales,
      store.cume_sales AS store_sales
    FROM web_v1 AS web
    FULL OUTER JOIN store_v1 AS store
      ON (
        web.item_sk = store.item_sk AND web.d_date = store.d_date
      )
    WHERE
      (
        web.item_sk = store.item_sk AND web.d_date = store.d_date
      )
  ) AS x
) AS y
WHERE
  web_cumulative > store_cumulative
ORDER BY
  item_sk,
  d_date
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['51'])

Source

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