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