| ==== |
| ---- QUERY: TPCDS-Q70 |
| |
| with results as |
| ( select |
| sum(ss_net_profit) as total_sum ,s_state ,s_county, 0 as gstate, 0 as g_county |
| from |
| store_sales |
| ,date_dim d1 |
| ,store |
| where |
| -- d1.d_year = [YEAR] |
| d1.d_month_seq between 1209 and 1209+11 |
| and d1.d_date_sk = ss_sold_date_sk |
| and s_store_sk = ss_store_sk |
| and s_state in |
| ( select s_state |
| from (select s_state as s_state, |
| rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking |
| from store_sales, store, date_dim |
| where d_month_seq between 1209 and 1209+11 |
| -- d_year =[YEAR] |
| and d_date_sk = ss_sold_date_sk |
| and s_store_sk = ss_store_sk |
| group by s_state |
| ) tmp1 |
| where ranking <= 5) |
| group by s_state,s_county) , |
| |
| results_rollup as |
| ( select total_sum ,s_state ,s_county, 0 as g_state, 0 as g_county, 0 as lochierarchy from results |
| union |
| select sum(total_sum) as total_sum,s_state, NULL as s_county, 0 as g_state, 1 as g_county, 1 as lochierarchy from results group by s_state |
| union |
| select sum(total_sum) as total_sum ,NULL as s_state ,NULL as s_county, 1 as g_state, 1 as g_county, 2 as lochierarchy from results) |
| |
| select total_sum ,s_state ,s_county, lochierarchy |
| ,rank() over ( |
| partition by lochierarchy, |
| case when g_county = 0 then s_state end |
| order by total_sum desc) as rank_within_parent |
| from results_rollup |
| order by |
| lochierarchy desc |
| ,case when lochierarchy = 0 then s_state end |
| ,rank_within_parent ; |
| ---- RESULTS |
| ---- TYPES |
| INT, INT, STRING, DECIMAL |
| ==== |
