0001 WITH v1 AS (
0002 SELECT
0003 i_category,
0004 i_brand,
0005 cc_name,
0006 d_year,
0007 d_moy,
0008 sum(cs_sales_price) sum_sales,
0009 avg(sum(cs_sales_price))
0010 OVER
0011 (PARTITION BY i_category, i_brand, cc_name, d_year)
0012 avg_monthly_sales,
0013 rank()
0014 OVER
0015 (PARTITION BY i_category, i_brand, cc_name
0016 ORDER BY d_year, d_moy) rn
0017 FROM item, catalog_sales, date_dim, call_center
0018 WHERE cs_item_sk = i_item_sk AND
0019 cs_sold_date_sk = d_date_sk AND
0020 cc_call_center_sk = cs_call_center_sk AND
0021 (
0022 d_year = 1999 OR
0023 (d_year = 1999 - 1 AND d_moy = 12) OR
0024 (d_year = 1999 + 1 AND d_moy = 1)
0025 )
0026 GROUP BY i_category, i_brand,
0027 cc_name, d_year, d_moy),
0028 v2 AS (
0029 SELECT
0030 v1.i_category,
0031 v1.i_brand,
0032 v1.cc_name,
0033 v1.d_year,
0034 v1.d_moy,
0035 v1.avg_monthly_sales,
0036 v1.sum_sales,
0037 v1_lag.sum_sales psum,
0038 v1_lead.sum_sales nsum
0039 FROM v1, v1 v1_lag, v1 v1_lead
0040 WHERE v1.i_category = v1_lag.i_category AND
0041 v1.i_category = v1_lead.i_category AND
0042 v1.i_brand = v1_lag.i_brand AND
0043 v1.i_brand = v1_lead.i_brand AND
0044 v1.cc_name = v1_lag.cc_name AND
0045 v1.cc_name = v1_lead.cc_name AND
0046 v1.rn = v1_lag.rn + 1 AND
0047 v1.rn = v1_lead.rn - 1)
0048 SELECT *
0049 FROM v2
0050 WHERE d_year = 1999 AND
0051 avg_monthly_sales > 0 AND
0052 CASE WHEN avg_monthly_sales > 0
0053 THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
0054 ELSE NULL END > 0.1
0055 ORDER BY sum_sales - avg_monthly_sales, 3
0056 LIMIT 100
