0001
0002 with results as (
0003 select
0004 sum(ws_net_paid) as total_sum,
0005 i_category, i_class,
0006 0 as g_category,
0007 0 as g_class
0008 from
0009 web_sales, date_dim d1, item
0010 where
0011 d1.d_month_seq between 1212 and 1212 + 11
0012 and d1.d_date_sk = ws_sold_date_sk
0013 and i_item_sk = ws_item_sk
0014 group by
0015 i_category, i_class),
0016 results_rollup as(
0017 select
0018 total_sum,
0019 i_category,
0020 i_class,
0021 g_category,
0022 g_class,
0023 0 as lochierarchy
0024 from
0025 results
0026 union
0027 select
0028 sum(total_sum) as total_sum,
0029 i_category,
0030 NULL as i_class,
0031 0 as g_category,
0032 1 as g_class,
0033 1 as lochierarchy
0034 from
0035 results
0036 group by
0037 i_category
0038 union
0039 select
0040 sum(total_sum) as total_sum,
0041 NULL as i_category,
0042 NULL as i_class,
0043 1 as g_category,
0044 1 as g_class,
0045 2 as lochierarchy
0046 from
0047 results)
0048 select
0049 total_sum,
0050 i_category ,i_class, lochierarchy,
0051 rank() over (
0052 partition by lochierarchy,
0053 case when g_class = 0 then i_category end
0054 order by total_sum desc) as rank_within_parent
0055 from
0056 results_rollup
0057 order by
0058 lochierarchy desc,
0059 case when lochierarchy = 0 then i_category end,
0060 rank_within_parent
0061 limit 100
