| SELECT * |
| FROM (SELECT |
| i_manager_id, |
| sum(ss_sales_price) sum_sales, |
| avg(sum(ss_sales_price)) |
| OVER (PARTITION BY i_manager_id) avg_monthly_sales |
| FROM item |
| , store_sales |
| , date_dim |
| , store |
| WHERE ss_item_sk = i_item_sk |
| AND ss_sold_date_sk = d_date_sk |
| AND ss_store_sk = s_store_sk |
| AND d_month_seq IN (1200, 1200 + 1, 1200 + 2, 1200 + 3, 1200 + 4, 1200 + 5, 1200 + 6, 1200 + 7, |
| 1200 + 8, 1200 + 9, 1200 + 10, 1200 + 11) |
| AND ((i_category IN ('Books', 'Children', 'Electronics') |
| AND i_class IN ('personal', 'portable', 'refernece', 'self-help') |
| AND i_brand IN ('scholaramalgamalg #14', 'scholaramalgamalg #7', |
| 'exportiunivamalg #9', 'scholaramalgamalg #9')) |
| OR (i_category IN ('Women', 'Music', 'Men') |
| AND i_class IN ('accessories', 'classical', 'fragrances', 'pants') |
| AND i_brand IN ('amalgimporto #1', 'edu packscholar #1', 'exportiimporto #1', |
| 'importoamalg #1'))) |
| GROUP BY i_manager_id, d_moy) tmp1 |
| WHERE CASE WHEN avg_monthly_sales > 0 |
| THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales |
| ELSE NULL END > 0.1 |
| ORDER BY i_manager_id |
| , avg_monthly_sales |
| , sum_sales |
| LIMIT 100 |
