-- start query 56 in stream 0 using template query89.tpl
select  *
from(
select i_category, i_class, i_brand,
       s_store_name, s_company_name,
       d_moy,
       sum(ss_sales_price) sum_sales,
       avg(sum(ss_sales_price)) over
         (partition by i_category, i_brand, s_store_name, s_company_name)
         avg_monthly_sales
from bigdata_public_dataset.tpcds_10t.item, bigdata_public_dataset.tpcds_10t.store_sales, bigdata_public_dataset.tpcds_10t.date_dim, bigdata_public_dataset.tpcds_10t.store
where ss_item_sk = i_item_sk and
      ss_sold_date_sk = d_date_sk and
      ss_store_sk = s_store_sk and
      d_year in (1999) and
        ((i_category in ('Jewelry','Men','Children') and
          i_class in ('loose stones','accessories','school-uniforms')
         )
      or (i_category in ('Home','Shoes','Books') and
          i_class in ('bathroom','kids','history') 
        ))
group by i_category, i_class, i_brand,
         s_store_name, s_company_name, d_moy) tmp1
where 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, s_store_name
limit 100;

-- end query 56 in stream 0 using template query89.tpl
