0001 SELECT *
0002 FROM
0003 (SELECT
0004 i_category,
0005 i_class,
0006 i_brand,
0007 i_product_name,
0008 d_year,
0009 d_qoy,
0010 d_moy,
0011 s_store_id,
0012 sumsales,
0013 rank()
0014 OVER (PARTITION BY i_category
0015 ORDER BY sumsales DESC) rk
0016 FROM
0017 (SELECT
0018 i_category,
0019 i_class,
0020 i_brand,
0021 i_product_name,
0022 d_year,
0023 d_qoy,
0024 d_moy,
0025 s_store_id,
0026 sum(coalesce(ss_sales_price * ss_quantity, 0)) sumsales
0027 FROM store_sales, date_dim, store, item
0028 WHERE ss_sold_date_sk = d_date_sk
0029 AND ss_item_sk = i_item_sk
0030 AND ss_store_sk = s_store_sk
0031 AND d_month_seq BETWEEN 1200 AND 1200 + 11
0032 GROUP BY ROLLUP (i_category, i_class, i_brand, i_product_name, d_year, d_qoy,
0033 d_moy, s_store_id)) dw1) dw2
0034 WHERE rk <= 100
0035 ORDER BY
0036 i_category, i_class, i_brand, i_product_name, d_year,
0037 d_qoy, d_moy, s_store_id, sumsales, rk
0038 LIMIT 100
