text: -- RNGSEED: 1
  
  with v1 as(
   select i_category, i_brand,
          cc_name,
          d_year, d_moy,
          sum(cs_sales_price) sum_sales,
          avg(sum(cs_sales_price)) over
            (partition by i_category, i_brand,
                       cc_name, d_year)
            avg_monthly_sales,
          rank() over
            (partition by i_category, i_brand,
                       cc_name
             order by d_year, d_moy) rn
   from item, catalog_sales, date_dim, call_center
   where cs_item_sk = i_item_sk and
         cs_sold_date_sk = d_date_sk and
         cc_call_center_sk= cs_call_center_sk and
         (
           d_year = 2001 or
           ( d_year = 2001-1 and d_moy =12) or
           ( d_year = 2001+1 and d_moy =1)
         )
   group by i_category, i_brand,
            cc_name , d_year, d_moy),
   v2 as(
   select v1.i_category, v1.i_brand, v1.cc_name
          ,v1.d_year, v1.d_moy
          ,v1.avg_monthly_sales
          ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
   from v1, v1 v1_lag, v1 v1_lead
   where v1.i_category = v1_lag.i_category and
         v1.i_category = v1_lead.i_category and
         v1.i_brand = v1_lag.i_brand and
         v1.i_brand = v1_lead.i_brand and
         v1. cc_name = v1_lag. cc_name and
         v1. cc_name = v1_lead. cc_name and
         v1.rn = v1_lag.rn + 1 and
         v1.rn = v1_lead.rn - 1)
    select  *
   from v2
   where  d_year = 2001 and
          avg_monthly_sales > 0 and
          case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
   order by sum_sales - avg_monthly_sales, 3
   limit 100;
  