text: -- Licensed to the Apache Software Foundation (ASF) under one
  -- or more contributor license agreements.  See the NOTICE file
  -- distributed with this work for additional information
  -- regarding copyright ownership.  The ASF licenses this file
  -- to you under the Apache License, Version 2.0 (the
  -- "License"); you may not use this file except in compliance
  -- with the License.  You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  
  select  *
  from (select i_category
              ,i_class
              ,i_brand
              ,i_product_name
              ,d_year
              ,d_qoy
              ,d_moy
              ,s_store_id
              ,sumsales
              ,rank() over (partition by i_category order by sumsales desc) rk
        from (select i_category
                    ,i_class
                    ,i_brand
                    ,i_product_name
                    ,d_year
                    ,d_qoy
                    ,d_moy
                    ,s_store_id
                    ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
              from store_sales
                  ,date_dim
                  ,store
                  ,item
         where  ss_sold_date_sk=d_date_sk
            and ss_item_sk=i_item_sk
            and ss_store_sk = s_store_sk
            and d_month_seq between 1212 and 1212+11
         group by  rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
  where rk <= 100
  order by i_category
          ,i_class
          ,i_brand
          ,i_product_name
          ,d_year
          ,d_qoy
          ,d_moy
          ,s_store_id
          ,sumsales
          ,rk
  limit 100
  