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