TPC-DS Q67

SELECT
TOP 100
  *
FROM (
  SELECT
    i_category AS i_category,
    i_class AS i_class,
    i_brand AS i_brand,
    i_product_name AS i_product_name,
    d_year AS d_year,
    d_qoy AS d_qoy,
    d_moy AS d_moy,
    s_store_id AS s_store_id,
    sumsales AS sumsales,
    RANK() OVER (PARTITION BY i_category ORDER BY sumsales DESC) AS rk
  FROM (
    SELECT
      i_category AS i_category,
      i_class AS i_class,
      i_brand AS i_brand,
      i_product_name AS i_product_name,
      d_year AS d_year,
      d_qoy AS d_qoy,
      d_moy AS d_moy,
      s_store_id AS s_store_id,
      SUM(COALESCE(ss_sales_price * ss_quantity, 0)) AS sumsales
    FROM store_sales, date_dim, store, item
    WHERE
      ss_sold_date_sk = d_date_sk
      AND ss_item_sk = i_item_sk
      AND ss_store_sk = s_store_sk
      AND d_month_seq BETWEEN 1220 AND 1220 + 11
    GROUP BY
      ROLLUP (
        i_category,
        i_class,
        i_brand,
        i_product_name,
        d_year,
        d_qoy,
        d_moy,
        s_store_id
      )
  ) AS dw1
) AS dw2
WHERE
  rk <= 100
ORDER BY
  i_category,
  i_class,
  i_brand,
  i_product_name,
  d_year,
  d_qoy,
  d_moy,
  s_store_id,
  sumsales,
  rk;