text: 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 = 1999 or
              (d_year = 1999 - 1 and d_moy = 12) or
              (d_year = 1999 + 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)
  insert overwrite table query57
  select *
  from v2
  where d_year = 1999
    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;
  