TPC-DS-OBT Q77

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH ss AS (
  SELECT
    obt.store_s_store_sk AS s_store_sk,
    SUM(obt.ext_sales_price) AS sales,
    SUM(obt.net_profit) 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 channel = 'store'
  GROUP BY
    obt.store_s_store_sk
), sr AS (
  SELECT
    obt.store_s_store_sk AS s_store_sk,
    SUM(obt.return_amount) AS returns,
    SUM(obt.return_net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.returned_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 channel = 'store'
  GROUP BY
    obt.store_s_store_sk
), cs AS (
  SELECT
    obt.call_center_sk,
    SUM(obt.ext_sales_price) AS sales,
    SUM(obt.net_profit) 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 channel = 'catalog'
  GROUP BY
    obt.call_center_sk
), cr AS (
  SELECT
    obt.call_center_sk,
    SUM(obt.return_amount) AS returns,
    SUM(obt.return_net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.returned_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 channel = 'catalog'
  GROUP BY
    obt.call_center_sk
), ws AS (
  SELECT
    obt.web_page_wp_web_page_sk AS wp_web_page_sk,
    SUM(obt.ext_sales_price) AS sales,
    SUM(obt.net_profit) 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.web_page_sk = obt.web_page_wp_web_page_sk
    )
    AND channel = 'web'
  GROUP BY
    obt.web_page_wp_web_page_sk
), wr AS (
  SELECT
    obt.web_page_wp_web_page_sk AS wp_web_page_sk,
    SUM(obt.return_amount) AS returns,
    SUM(obt.return_net_loss) AS profit_loss
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.returned_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.web_page_sk = obt.web_page_wp_web_page_sk
    )
    AND channel = 'web'
  GROUP BY
    obt.web_page_wp_web_page_sk
)
SELECT
  channel,
  id,
  SUM(sales) AS sales,
  SUM(returns) AS returns,
  SUM(profit) AS profit
FROM (
  SELECT
    'store channel' AS channel,
    ss.s_store_sk AS id,
    sales,
    COALESCE(returns, 0) AS returns,
    (
      profit - COALESCE(profit_loss, 0)
    ) AS profit
  FROM ss
  LEFT JOIN sr
    ON ss.s_store_sk = sr.s_store_sk
  WHERE
    ss.s_store_sk = sr.s_store_sk
  UNION ALL
  SELECT
    'catalog channel' AS channel,
    cs.call_center_sk AS id,
    sales,
    returns,
    (
      profit - profit_loss
    ) AS profit
  FROM cs, cr
  UNION ALL
  SELECT
    'web channel' AS channel,
    ws.wp_web_page_sk AS id,
    sales,
    COALESCE(returns, 0) AS returns,
    (
      profit - COALESCE(profit_loss, 0)
    ) AS profit
  FROM ws
  LEFT JOIN wr
    ON ws.wp_web_page_sk = wr.wp_web_page_sk
  WHERE
    ws.wp_web_page_sk = wr.wp_web_page_sk
) 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['77'])

Source

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