TPC-DS-OBT Q83

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

Representative SQL

Rendered for DataFusion with default parameters.

WITH sr_items AS (
  SELECT
    obt.item_i_item_id AS item_id,
    SUM(obt.return_quantity) AS sr_item_qty
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.item_sk = obt.item_i_item_sk
      AND obt.sold_date_d_date IN (
        SELECT
          obt.sold_date_d_date
        FROM tpcds_sales_returns_obt AS obt
        WHERE
          obt.sold_date_d_week_seq IN (
            SELECT
              obt.sold_date_d_week_seq
            FROM tpcds_sales_returns_obt AS obt
            WHERE
              obt.sold_date_d_date IN ('1998-01-01', '1998-01-01', '1998-01-01')
              AND channel IN ('catalog', 'store', 'web')
          )
          AND channel IN ('catalog', 'store', 'web')
      )
      AND obt.returned_date_sk = obt.sold_date_d_date_sk
    )
    AND channel = 'store'
  GROUP BY
    obt.item_i_item_id
), cr_items AS (
  SELECT
    obt.item_i_item_id AS item_id,
    SUM(obt.return_quantity) AS cr_item_qty
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.item_sk = obt.item_i_item_sk
      AND obt.sold_date_d_date IN (
        SELECT
          obt.sold_date_d_date
        FROM tpcds_sales_returns_obt AS obt
        WHERE
          obt.sold_date_d_week_seq IN (
            SELECT
              obt.sold_date_d_week_seq
            FROM tpcds_sales_returns_obt AS obt
            WHERE
              obt.sold_date_d_date IN ('1998-01-01', '1998-01-01', '1998-01-01')
              AND channel IN ('catalog', 'store', 'web')
          )
          AND channel IN ('catalog', 'store', 'web')
      )
      AND obt.returned_date_sk = obt.sold_date_d_date_sk
    )
    AND channel = 'catalog'
  GROUP BY
    obt.item_i_item_id
), wr_items AS (
  SELECT
    obt.item_i_item_id AS item_id,
    SUM(obt.return_quantity) AS wr_item_qty
  FROM tpcds_sales_returns_obt AS obt
  WHERE
    (
      obt.item_sk = obt.item_i_item_sk
      AND obt.sold_date_d_date IN (
        SELECT
          obt.sold_date_d_date
        FROM tpcds_sales_returns_obt AS obt
        WHERE
          obt.sold_date_d_week_seq IN (
            SELECT
              obt.sold_date_d_week_seq
            FROM tpcds_sales_returns_obt AS obt
            WHERE
              obt.sold_date_d_date IN ('1998-01-01', '1998-01-01', '1998-01-01')
              AND channel IN ('catalog', 'store', 'web')
          )
          AND channel IN ('catalog', 'store', 'web')
      )
      AND obt.returned_date_sk = obt.sold_date_d_date_sk
    )
    AND channel = 'web'
  GROUP BY
    obt.item_i_item_id
)
SELECT
  sr_items.item_id,
  sr_item_qty,
  CAST(CAST(sr_item_qty AS DOUBLE PRECISION) / (
    sr_item_qty + cr_item_qty + wr_item_qty
  ) AS DOUBLE PRECISION) / 3.0 * 100 AS sr_dev,
  cr_item_qty,
  CAST(CAST(cr_item_qty AS DOUBLE PRECISION) / (
    sr_item_qty + cr_item_qty + wr_item_qty
  ) AS DOUBLE PRECISION) / 3.0 * 100 AS cr_dev,
  wr_item_qty,
  CAST(CAST(wr_item_qty AS DOUBLE PRECISION) / (
    sr_item_qty + cr_item_qty + wr_item_qty
  ) AS DOUBLE PRECISION) / 3.0 * 100 AS wr_dev,
  CAST((
    sr_item_qty + cr_item_qty + wr_item_qty
  ) AS DOUBLE PRECISION) / 3.0 AS average
FROM sr_items, cr_items, wr_items
WHERE
  sr_items.item_id = cr_items.item_id AND sr_items.item_id = wr_items.item_id
ORDER BY
  sr_items.item_id,
  sr_item_qty
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['83'])

Source

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