0001 SELECT *
0002 FROM
0003 (SELECT
0004 i_manufact_id,
0005 sum(ss_sales_price) sum_sales,
0006 avg(sum(ss_sales_price))
0007 OVER (PARTITION BY i_manufact_id) avg_quarterly_sales
0008 FROM item, store_sales, date_dim, store
0009 WHERE ss_item_sk = i_item_sk AND
0010 ss_sold_date_sk = d_date_sk AND
0011 ss_store_sk = s_store_sk AND
0012 d_month_seq IN (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6,
0013 1200 + 7, 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11) AND
0014 ((i_category IN ('Books', 'Children', 'Electronics') AND
0015 i_class IN ('personal', 'portable', 'reference', 'self-help') AND
0016 i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7',
0017 'exportiunivamalg #9', 'scholaramalgamalg #9'))
0018 OR
0019 (i_category IN ('Women', 'Music', 'Men') AND
0020 i_class IN ('accessories', 'classical', 'fragrances', 'pants') AND
0021 i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1',
0022 'importoamalg #1')))
0023 GROUP BY i_manufact_id, d_qoy) tmp1
0024 WHERE CASE WHEN avg_quarterly_sales > 0
0025 THEN abs(sum_sales - avg_quarterly_sales) / avg_quarterly_sales
0026 ELSE NULL END > 0.1
0027 ORDER BY avg_quarterly_sales,
0028 sum_sales,
0029 i_manufact_id
0030 LIMIT 100
