TPC-DS-OBT Q44

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

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  asceding.rnk,
  item_i_product_name AS best_performing,
  item_i_product_name AS worst_performing
FROM tpcds_sales_returns_obt AS obt
CROSS JOIN (
  SELECT
    *
  FROM (
    SELECT
      item_sk,
      RANK() OVER (ORDER BY rank_col ASC) AS rnk
    FROM (
      SELECT
        obt.item_sk AS item_sk,
        AVG(obt.net_profit) AS rank_col
      FROM tpcds_sales_returns_obt AS obt
      WHERE
        obt.store_sk = 1 AND channel = 'store'
      GROUP BY
        obt.item_sk
      HAVING
        AVG(obt.net_profit) > 0.9 * (
          SELECT
            AVG(obt.net_profit) AS rank_col
          FROM tpcds_sales_returns_obt AS obt
          WHERE
            (
              obt.store_sk = 1 AND ship_customer_sk IS NULL
            ) AND channel = 'store'
          GROUP BY
            obt.store_sk
        )
    ) AS V1
  ) AS V11
  WHERE
    rnk < 11
) AS asceding
CROSS JOIN (
  SELECT
    *
  FROM (
    SELECT
      item_sk,
      RANK() OVER (ORDER BY rank_col DESC) AS rnk
    FROM (
      SELECT
        obt.item_sk AS item_sk,
        AVG(obt.net_profit) AS rank_col
      FROM tpcds_sales_returns_obt AS obt
      WHERE
        obt.store_sk = 1 AND channel = 'store'
      GROUP BY
        obt.item_sk
      HAVING
        AVG(obt.net_profit) > 0.9 * (
          SELECT
            AVG(obt.net_profit) AS rank_col
          FROM tpcds_sales_returns_obt AS obt
          WHERE
            (
              obt.store_sk = 1 AND ship_customer_sk IS NULL
            ) AND channel = 'store'
          GROUP BY
            obt.store_sk
        )
    ) AS V2
  ) AS V21
  WHERE
    rnk < 11
) AS descending
WHERE
  (
    asceding.rnk = descending.rnk
    AND obt.item_sk = asceding.item_sk
    AND obt.item_sk = descending.item_sk
  )
  AND channel = 'store'
ORDER BY
  asceding.rnk
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['44'])

Source

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