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