0001 SELECT
0002 sum(ws_net_paid) AS total_sum,
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(ws_net_paid) DESC) AS rank_within_parent
0012 FROM
0013 web_sales, date_dim d1, item
0014 WHERE
0015 d1.d_month_seq BETWEEN 1200 AND 1200 + 11
0016 AND d1.d_date_sk = ws_sold_date_sk
0017 AND i_item_sk = ws_item_sk
0018 GROUP BY ROLLUP (i_category, i_class)
0019 ORDER BY
0020 lochierarchy DESC,
0021 CASE WHEN lochierarchy = 0
0022 THEN i_category END,
0023 rank_within_parent
0024 LIMIT 100
