0001
0002 with results as (
0003 select
0004 sum(ss_net_profit) as ss_net_profit,
0005 sum(ss_ext_sales_price) as ss_ext_sales_price,
0006 sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin,
0007 i_category,
0008 i_class,
0009 0 as g_category,
0010 0 as g_class
0011 from
0012 store_sales,
0013 date_dim d1,
0014 item,
0015 store
0016 where
0017 d1.d_year = 2001
0018 and d1.d_date_sk = ss_sold_date_sk
0019 and i_item_sk = ss_item_sk
0020 and s_store_sk = ss_store_sk
0021 and s_state in ('TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN', 'TN')
0022 group by
0023 i_category,
0024 i_class),
0025 results_rollup as (
0026 select
0027 gross_margin,
0028 i_category,
0029 i_class,
0030 0 as t_category,
0031 0 as t_class,
0032 0 as lochierarchy
0033 from
0034 results
0035 union
0036 select
0037 sum(ss_net_profit) / sum(ss_ext_sales_price) as gross_margin,
0038 i_category, NULL AS i_class,
0039 0 as t_category,
0040 1 as t_class,
0041 1 as lochierarchy
0042 from
0043 results
0044 group by
0045 i_category
0046 union
0047 select
0048 sum(ss_net_profit) / sum(ss_ext_sales_price) as gross_margin,
0049 NULL AS i_category,
0050 NULL AS i_class,
0051 1 as t_category,
0052 1 as t_class,
0053 2 as lochierarchy
0054 from
0055 results)
0056 select
0057 gross_margin,
0058 i_category,
0059 i_class,
0060 lochierarchy,
0061 rank() over (
0062 partition by lochierarchy, case when t_class = 0 then i_category end
0063 order by gross_margin asc) as rank_within_parent
0064 from
0065 results_rollup
0066 order by
0067 lochierarchy desc,
0068 case when lochierarchy = 0 then i_category end,
0069 rank_within_parent
0070 limit 100
