0001
0002 with results as (
0003 select
0004 sum(ss_net_profit) as total_sum,
0005 s_state ,s_county,
0006 0 as gstate,
0007 0 as g_county
0008 from
0009 store_sales, date_dim d1, store
0010 where
0011 d1.d_month_seq between 1212 and 1212 + 11
0012 and d1.d_date_sk = ss_sold_date_sk
0013 and s_store_sk = ss_store_sk
0014 and s_state in (
0015 select s_state
0016 from (
0017 select
0018 s_state as s_state,
0019 rank() over (partition by s_state order by sum(ss_net_profit) desc) as ranking
0020 from store_sales, store, date_dim
0021 where d_month_seq between 1212 and 1212 + 11
0022 and d_date_sk = ss_sold_date_sk
0023 and s_store_sk = ss_store_sk
0024 group by s_state) tmp1
0025 where ranking <= 5)
0026 group by
0027 s_state, s_county),
0028 results_rollup as (
0029 select
0030 total_sum,
0031 s_state,
0032 s_county,
0033 0 as g_state,
0034 0 as g_county,
0035 0 as lochierarchy
0036 from results
0037 union
0038 select
0039 sum(total_sum) as total_sum,s_state,
0040 NULL as s_county,
0041 0 as g_state,
0042 1 as g_county,
0043 1 as lochierarchy
0044 from results
0045 group by s_state
0046 union
0047 select
0048 sum(total_sum) as total_sum,
0049 NULL as s_state,
0050 NULL as s_county,
0051 1 as g_state,
0052 1 as g_county,
0053 2 as lochierarchy
0054 from results)
0055 select
0056 total_sum,
0057 s_state,
0058 s_county,
0059 lochierarchy,
0060 rank() over (
0061 partition by lochierarchy,
0062 case when g_county = 0 then s_state end
0063 order by total_sum desc) as rank_within_parent
0064 from
0065 results_rollup
0066 order by
0067 lochierarchy desc,
0068 case when lochierarchy = 0 then s_state end,
0069 rank_within_parent
0070 limit 100
