TPC-DS-OBT Q80

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH ssr AS (
  SELECT
    obt.store_s_store_id AS store_id,
    SUM(obt.ext_sales_price) AS sales,
    SUM(COALESCE(obt.return_amount, 0)) AS returns,
    SUM(obt.net_profit - COALESCE(obt.return_net_loss, 0)) AS profit
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      (
        obt.sold_date_sk = obt.sold_date_d_date_sk
        AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
          CAST('1998-01-01' AS DATE) + INTERVAL '30 DAY'
        )
        AND obt.store_sk = obt.store_s_store_sk
        AND obt.item_sk = obt.item_i_item_sk
        AND obt.item_i_current_price > 50
        AND obt.promo_sk = obt.promo_p_promo_sk
        AND obt.promo_p_channel_tv = 'N'
      )
      AND (
        obt.item_sk = obt.item_sk AND obt.sale_id = obt.sale_id
      )
    )
    AND channel = 'store'
  GROUP BY
    obt.store_s_store_id
), csr AS (
  SELECT
    obt.catalog_page_cp_catalog_page_id AS catalog_page_id,
    SUM(obt.ext_sales_price) AS sales,
    SUM(COALESCE(obt.return_amount, 0)) AS returns,
    SUM(obt.net_profit - COALESCE(obt.return_net_loss, 0)) AS profit
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      (
        obt.sold_date_sk = obt.sold_date_d_date_sk
        AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
          CAST('1998-01-01' AS DATE) + INTERVAL '30 DAY'
        )
        AND obt.catalog_page_sk = obt.catalog_page_cp_catalog_page_sk
        AND obt.item_sk = obt.item_i_item_sk
        AND obt.item_i_current_price > 50
        AND obt.promo_sk = obt.promo_p_promo_sk
        AND obt.promo_p_channel_tv = 'N'
      )
      AND (
        obt.item_sk = obt.item_sk AND obt.sale_id = obt.sale_id
      )
    )
    AND channel = 'catalog'
  GROUP BY
    obt.catalog_page_cp_catalog_page_id
), wsr AS (
  SELECT
    obt.web_site_web_site_id,
    SUM(obt.ext_sales_price) AS sales,
    SUM(COALESCE(obt.return_amount, 0)) AS returns,
    SUM(obt.net_profit - COALESCE(obt.return_net_loss, 0)) AS profit
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      (
        obt.sold_date_sk = obt.sold_date_d_date_sk
        AND obt.sold_date_d_date BETWEEN CAST('1998-01-01' AS DATE) AND (
          CAST('1998-01-01' AS DATE) + INTERVAL '30 DAY'
        )
        AND web_site_web_site_sk = obt.web_site_web_site_sk
        AND obt.item_sk = obt.item_i_item_sk
        AND obt.item_i_current_price > 50
        AND obt.promo_sk = obt.promo_p_promo_sk
        AND obt.promo_p_channel_tv = 'N'
      )
      AND (
        obt.item_sk = obt.item_sk AND obt.sale_id = obt.sale_id
      )
    )
    AND channel = 'web'
  GROUP BY
    obt.web_site_web_site_id
)
SELECT
  channel,
  id,
  SUM(sales) AS sales,
  SUM(returns) AS returns,
  SUM(profit) AS profit
FROM (
  SELECT
    'store channel' AS channel,
    'store' || store_id AS id,
    sales,
    returns,
    profit
  FROM ssr
  UNION ALL
  SELECT
    'catalog channel' AS channel,
    'catalog_page' || catalog_page_id AS id,
    sales,
    returns,
    profit
  FROM csr
  UNION ALL
  SELECT
    'web channel' AS channel,
    'web_site' || web_site_web_site_id AS id,
    sales,
    returns,
    profit
  FROM wsr
) AS x
GROUP BY
  ROLLUP (
    channel,
    id
  )
ORDER BY
  channel,
  id
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['80'])

Source

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