0001 SELECT *
0002 FROM (
0003 SELECT
0004 i_category,
0005 i_class,
0006 i_brand,
0007 s_store_name,
0008 s_company_name,
0009 d_moy,
0010 sum(ss_sales_price) sum_sales,
0011 avg(sum(ss_sales_price))
0012 OVER
0013 (PARTITION BY i_category, i_brand, s_store_name, s_company_name)
0014 avg_monthly_sales
0015 FROM item, store_sales, date_dim, store
0016 WHERE ss_item_sk = i_item_sk AND
0017 ss_sold_date_sk = d_date_sk AND
0018 ss_store_sk = s_store_sk AND
0019 d_year IN (1999) AND
0020 ((i_category IN ('Books', 'Electronics', 'Sports') AND
0021 i_class IN ('computers', 'stereo', 'football'))
0022 OR (i_category IN ('Men', 'Jewelry', 'Women') AND
0023 i_class IN ('shirts', 'birdal', 'dresses')))
0024 GROUP BY i_category, i_class, i_brand,
0025 s_store_name, s_company_name, d_moy) tmp1
0026 WHERE CASE WHEN (avg_monthly_sales <> 0)
0027 THEN (abs(sum_sales - avg_monthly_sales) / avg_monthly_sales)
0028 ELSE NULL END > 0.1
0029 ORDER BY sum_sales - avg_monthly_sales, s_store_name
0030 LIMIT 100
