| 123456789101112131415161718192021222324252627282930313233343536373839404142434445 |
- 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 store_sales.ss_sold_date_sk=date_dim.d_date_sk
- and store_sales.ss_item_sk=item.i_item_sk
- and store_sales.ss_store_sk = store.s_store_sk
- and d_month_seq between 1193 and 1193+11
- and ss_sold_date between '1999-06-01' and '2000-05-31'
- group by i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id with rollup)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;
|
