0001 SELECT
0002 sum(ss_net_profit) / sum(ss_ext_sales_price) AS gross_margin,
0003 i_category,
0004 i_class,
0005 grouping(i_category) + grouping(i_class) AS lochierarchy,
0006 rank()
0007 OVER (
0008 PARTITION BY grouping(i_category) + grouping(i_class),
0009 CASE WHEN grouping(i_class) = 0
0010 THEN i_category END
0011 ORDER BY sum(ss_net_profit) / sum(ss_ext_sales_price) ASC) AS rank_within_parent
0012 FROM
0013 store_sales, date_dim d1, item, store
0014 WHERE
0015 d1.d_year = 2001
0016 AND d1.d_date_sk = ss_sold_date_sk
0017 AND i_item_sk = ss_item_sk
0018 AND s_store_sk = ss_store_sk
0019 AND s_state IN ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
0020 GROUP BY ROLLUP (i_category, i_class)
0021 ORDER BY
0022 lochierarchy DESC
0023 , CASE WHEN lochierarchy = 0
0024 THEN i_category END
0025 , rank_within_parent
0026 LIMIT 100
