query98.sql

-- start query 98 in stream 0 using template query98.tpl 
SELECT i_item_id, 
       i_item_desc, 
       i_category, 
       i_class, 
       i_current_price, 
       Sum(ss_ext_sales_price)                                   AS itemrevenue, 
       Sum(ss_ext_sales_price) * 100 / Sum(Sum(ss_ext_sales_price)) 
                                         OVER ( 
                                           PARTITION BY i_class) AS revenueratio 
FROM   store_sales, 
       item, 
       date_dim 
WHERE  ss_item_sk = i_item_sk 
       AND i_category IN ( 'Men', 'Home', 'Electronics' ) 
       AND ss_sold_date_sk = d_date_sk 
       AND d_date BETWEEN CAST('2000-05-18' AS DATE) AND ( 
                          CAST('2000-05-18' AS DATE) + INTERVAL '30' DAY ) 
GROUP  BY i_item_id, 
          i_item_desc, 
          i_category, 
          i_class, 
          i_current_price 
ORDER  BY i_category, 
          i_class, 
          i_item_id, 
          i_item_desc, 
          revenueratio;