0001 SELECT
0002 sum(ss_net_profit) AS total_sum,
0003 s_state,
0004 s_county,
0005 grouping(s_state) + grouping(s_county) AS lochierarchy,
0006 rank()
0007 OVER (
0008 PARTITION BY grouping(s_state) + grouping(s_county),
0009 CASE WHEN grouping(s_county) = 0
0010 THEN s_state END
0011 ORDER BY sum(ss_net_profit) DESC) AS rank_within_parent
0012 FROM
0013 store_sales, date_dim d1, store
0014 WHERE
0015 d1.d_month_seq BETWEEN 1200 AND 1200 + 11
0016 AND d1.d_date_sk = ss_sold_date_sk
0017 AND s_store_sk = ss_store_sk
0018 AND s_state IN
0019 (SELECT s_state
0020 FROM
0021 (SELECT
0022 s_state AS s_state,
0023 rank()
0024 OVER (PARTITION BY s_state
0025 ORDER BY sum(ss_net_profit) DESC) AS ranking
0026 FROM store_sales, store, date_dim
0027 WHERE d_month_seq BETWEEN 1200 AND 1200 + 11
0028 AND d_date_sk = ss_sold_date_sk
0029 AND s_store_sk = ss_store_sk
0030 GROUP BY s_state) tmp1
0031 WHERE ranking <= 5)
0032 GROUP BY ROLLUP (s_state, s_county)
0033 ORDER BY
0034 lochierarchy DESC
0035 , CASE WHEN lochierarchy = 0
0036 THEN s_state END
0037 , rank_within_parent
0038 LIMIT 100
