blob: 7439ea1232b5bfc3e1d06f51c52fb62f4a06fdbb [file] [log] [blame]
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;