TPC-DS Q23A

WITH frequent_ss_items AS (
  SELECT
    SUBSTRING(i_item_desc, 1, 30) AS itemdesc,
    i_item_sk AS item_sk,
    d_date AS solddate,
    COUNT(*) AS cnt
  FROM store_sales, date_dim, item
  WHERE
    ss_sold_date_sk = d_date_sk
    AND ss_item_sk = i_item_sk
    AND d_year IN (1998, 1998 + 1, 1998 + 2, 1998 + 3)
  GROUP BY
    SUBSTRING(i_item_desc, 1, 30),
    i_item_sk,
    d_date
  HAVING
    COUNT(*) > 4
), max_store_sales AS (
  SELECT
    MAX(csales) AS tpcds_cmax
  FROM (
    SELECT
      c_customer_sk AS c_customer_sk,
      SUM(ss_quantity * ss_sales_price) AS csales
    FROM store_sales, customer, date_dim
    WHERE
      ss_customer_sk = c_customer_sk
      AND ss_sold_date_sk = d_date_sk
      AND d_year IN (1998, 1998 + 1, 1998 + 2, 1998 + 3)
    GROUP BY
      c_customer_sk
  ) AS polaris_derived_1
), best_ss_customer AS (
  SELECT
    c_customer_sk AS c_customer_sk,
    SUM(ss_quantity * ss_sales_price) AS ssales
  FROM store_sales, customer
  WHERE
    ss_customer_sk = c_customer_sk
  GROUP BY
    c_customer_sk
  HAVING
    SUM(ss_quantity * ss_sales_price) > (
      95 / 100.0
    ) * (
      SELECT
        *
      FROM max_store_sales
    )
)
SELECT
TOP 100
  SUM(sales)
FROM (
  SELECT
    cs_quantity * cs_list_price AS sales
  FROM catalog_sales, date_dim
  WHERE
    d_year = 1998
    AND d_moy = 5
    AND cs_sold_date_sk = d_date_sk
    AND cs_item_sk IN (
      SELECT
        item_sk
      FROM frequent_ss_items
    )
    AND cs_bill_customer_sk IN (
      SELECT
        c_customer_sk
      FROM best_ss_customer
    )
  UNION ALL
  SELECT
    ws_quantity * ws_list_price AS sales
  FROM web_sales, date_dim
  WHERE
    d_year = 1998
    AND d_moy = 5
    AND ws_sold_date_sk = d_date_sk
    AND ws_item_sk IN (
      SELECT
        item_sk
      FROM frequent_ss_items
    )
    AND ws_bill_customer_sk IN (
      SELECT
        c_customer_sk
      FROM best_ss_customer
    )
) AS polaris_derived_0;