tpcds.query57.sql Maven / Gradle / Ivy

Go to download
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)
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