1WITH v1 AS ( 2 SELECT 3 i_category, 4 i_brand, 5 cc_name, 6 d_year, 7 d_moy, 8 sum(cs_sales_price) sum_sales, 9 avg(sum(cs_sales_price)) 10 OVER 11 (PARTITION BY i_category, i_brand, cc_name, d_year) 12 avg_monthly_sales, 13 rank() 14 OVER 15 (PARTITION BY i_category, i_brand, cc_name 16 ORDER BY d_year, d_moy) rn 17 FROM item, catalog_sales, date_dim, call_center 18 WHERE cs_item_sk = i_item_sk AND 19 cs_sold_date_sk = d_date_sk AND 20 cc_call_center_sk = cs_call_center_sk AND 21 ( 22 d_year = 1999 OR 23 (d_year = 1999 - 1 AND d_moy = 12) OR 24 (d_year = 1999 + 1 AND d_moy = 1) 25 ) 26 GROUP BY i_category, i_brand, 27 cc_name, d_year, d_moy), 28 v2 AS ( 29 SELECT 30 v1.i_category, 31 v1.i_brand, 32 v1.cc_name, 33 v1.d_year, 34 v1.d_moy, 35 v1.avg_monthly_sales, 36 v1.sum_sales, 37 v1_lag.sum_sales psum, 38 v1_lead.sum_sales nsum 39 FROM v1, v1 v1_lag, v1 v1_lead 40 WHERE v1.i_category = v1_lag.i_category AND 41 v1.i_category = v1_lead.i_category AND 42 v1.i_brand = v1_lag.i_brand AND 43 v1.i_brand = v1_lead.i_brand AND 44 v1.cc_name = v1_lag.cc_name AND 45 v1.cc_name = v1_lead.cc_name AND 46 v1.rn = v1_lag.rn + 1 AND 47 v1.rn = v1_lead.rn - 1) 48SELECT * 49FROM v2 50WHERE d_year = 1999 AND 51 avg_monthly_sales > 0 AND 52 CASE WHEN avg_monthly_sales > 0 53 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales 54 ELSE NULL END > 0.1 55ORDER BY sum_sales - avg_monthly_sales, 3 56LIMIT 100 57