0001
0002 with results as (
0003 select
0004 i_category,
0005 i_class,
0006 i_brand,
0007 i_product_name,
0008 d_year,
0009 d_qoy,
0010 d_moy,
0011 s_store_id,
0012 sum(coalesce(ss_sales_price * ss_quantity, 0)) sumsales
0013 from
0014 store_sales, date_dim, store, item
0015 where
0016 ss_sold_date_sk=d_date_sk
0017 and ss_item_sk=i_item_sk
0018 and ss_store_sk = s_store_sk
0019 and d_month_seq between 1212 and 1212 + 11
0020 group by
0021 i_category,
0022 i_class,
0023 i_brand,
0024 i_product_name,
0025 d_year,
0026 d_qoy,
0027 d_moy,
0028 s_store_id),
0029 results_rollup as (
0030 select
0031 i_category,
0032 i_class,
0033 i_brand,
0034 i_product_name,
0035 d_year,
0036 d_qoy,
0037 d_moy,
0038 s_store_id,
0039 sumsales
0040 from
0041 results
0042 union all
0043 select
0044 i_category,
0045 i_class,
0046 i_brand,
0047 i_product_name,
0048 d_year,
0049 d_qoy,
0050 d_moy,
0051 null s_store_id,
0052 sum(sumsales) sumsales
0053 from
0054 results
0055 group by
0056 i_category,
0057 i_class,
0058 i_brand,
0059 i_product_name,
0060 d_year,
0061 d_qoy,
0062 d_moy
0063 union all
0064 select
0065 i_category,
0066 i_class,
0067 i_brand,
0068 i_product_name,
0069 d_year,
0070 d_qoy,
0071 null d_moy,
0072 null s_store_id,
0073 sum(sumsales) sumsales
0074 from
0075 results
0076 group by
0077 i_category,
0078 i_class,
0079 i_brand,
0080 i_product_name,
0081 d_year,
0082 d_qoy
0083 union all
0084 select
0085 i_category,
0086 i_class,
0087 i_brand,
0088 i_product_name,
0089 d_year,
0090 null d_qoy,
0091 null d_moy,
0092 null s_store_id,
0093 sum(sumsales) sumsales
0094 from
0095 results
0096 group by
0097 i_category,
0098 i_class,
0099 i_brand,
0100 i_product_name,
0101 d_year
0102 union all
0103 select
0104 i_category,
0105 i_class,
0106 i_brand,
0107 i_product_name,
0108 null d_year,
0109 null d_qoy,
0110 null d_moy,
0111 null s_store_id,
0112 sum(sumsales) sumsales
0113 from
0114 results
0115 group by
0116 i_category,
0117 i_class,
0118 i_brand,
0119 i_product_name
0120 union all
0121 select
0122 i_category,
0123 i_class,
0124 i_brand,
0125 null i_product_name,
0126 null d_year,
0127 null d_qoy,
0128 null d_moy,
0129 null s_store_id,
0130 sum(sumsales) sumsales
0131 from
0132 results
0133 group by
0134 i_category,
0135 i_class,
0136 i_brand
0137 union all
0138 select
0139 i_category,
0140 i_class,
0141 null i_brand,
0142 null i_product_name,
0143 null d_year,
0144 null d_qoy,
0145 null d_moy,
0146 null s_store_id,
0147 sum(sumsales) sumsales
0148 from
0149 results
0150 group by
0151 i_category,
0152 i_class
0153 union all
0154 select
0155 i_category,
0156 null i_class,
0157 null i_brand,
0158 null i_product_name,
0159 null d_year,
0160 null d_qoy,
0161 null d_moy,
0162 null s_store_id,
0163 sum(sumsales) sumsales
0164 from results
0165 group by
0166 i_category
0167 union all
0168 select
0169 null i_category,
0170 null i_class,
0171 null i_brand,
0172 null i_product_name,
0173 null d_year,
0174 null d_qoy,
0175 null d_moy,
0176 null s_store_id,
0177 sum(sumsales) sumsales
0178 from
0179 results)
0180 select
0181 *
0182 from (
0183 select
0184 i_category,
0185 i_class,
0186 i_brand,
0187 i_product_name,
0188 d_year,
0189 d_qoy,
0190 d_moy,
0191 s_store_id,
0192 sumsales,
0193 rank() over (partition by i_category order by sumsales desc) rk
0194 from results_rollup) dw2
0195 where
0196 rk <= 100
0197 order by
0198 i_category,
0199 i_class,
0200 i_brand,
0201 i_product_name,
0202 d_year,
0203 d_qoy,
0204 d_moy,
0205 s_store_id,
0206 sumsales,
0207 rk
0208 limit 100
