use tpcds_holodesk_1;

--set holodesk.automerge.enabled=true;
--set holodesk.automerge.mergesize=22;
set ngmr.mapjoin.partitionprune=true;
set hive.mapjoin.smalltable.filesize=30000000;
set mapred.minreduce.tasks=71;
set hive.cbo.enable=true;
set inceptor.cbo.mdprovider.extended=true;
set inceptor.cbo.rowbased.mapjoin=true;
set inceptor.cbo.rowbased.mapjoin.reduce.factor=2;
set inceptor.cbo.joincount.min=0;
set ngmr.metacache.level=statement;
set hive.reducer.control.enable=true;
set inceptor.conf.validation=false;
set ngmr.windrunner.session.mapjoin.partition.prune=true;
set inceptor.filterjoin.enabled=false;
set inceptor.mapjoin.filter=true;
set hive.window.iterator.directly=true;
set hive.groupby.iter.single.rollup=true;
set inceptor.remove.constant.group.by=true;
set hive.reducer.control.max.reducenum=6011;
set inceptor.subquery.cse.enabled=true;

set inceptor.decimal.display.padding.zero=true;



with ssales as
(select c_last_name
      ,c_first_name
      ,s_store_name
      ,ca_state
      ,s_state
      ,i_color
      ,i_current_price
      ,i_manager_id
      ,i_units
      ,i_size
      ,sum(ss_net_paid) netpaid
from store_sales
    ,store_returns
    ,store
    ,item
    ,customer
    ,customer_address
where 
ss_ticket_number = sr_ticket_number
  and ss_item_sk = sr_item_sk
  and ss_customer_sk = c_customer_sk
  and ss_item_sk = i_item_sk
  and ss_store_sk = s_store_sk
  and c_current_addr_sk = ca_address_sk
  and c_birth_country <> upper(ca_country)
  and s_zip = ca_zip
  and s_market_id=8
group by c_last_name
        ,c_first_name
        ,s_store_name
        ,ca_state
        ,s_state
        ,i_color
        ,i_current_price
        ,i_manager_id
        ,i_units
        ,i_size)
select c_last_name
      ,c_first_name
      ,s_store_name
      ,sum(netpaid) paid
from ssales
where i_color = 'peach'
group by c_last_name
        ,c_first_name
        ,s_store_name
having sum(netpaid) > (select 0.05*avg(netpaid)
                                 from ssales)
order by c_last_name
        ,c_first_name
        ,s_store_name
;

with ssales as
(select c_last_name
      ,c_first_name
      ,s_store_name
      ,ca_state
      ,s_state
      ,i_color
      ,i_current_price
      ,i_manager_id
      ,i_units
      ,i_size
      ,sum(ss_net_paid) netpaid
from store_sales
    ,store_returns
    ,store
    ,item
    ,customer
    ,customer_address
where ss_ticket_number = sr_ticket_number
  and ss_item_sk = sr_item_sk
  and ss_customer_sk = c_customer_sk
  and ss_item_sk = i_item_sk
  and ss_store_sk = s_store_sk
  and c_current_addr_sk = ca_address_sk
  and c_birth_country <> upper(ca_country)
  and s_zip = ca_zip
  and s_market_id = 8
group by c_last_name
        ,c_first_name
        ,s_store_name
        ,ca_state
        ,s_state
        ,i_color
        ,i_current_price
        ,i_manager_id
        ,i_units
        ,i_size)
select c_last_name
      ,c_first_name
      ,s_store_name
      ,sum(netpaid) paid
from ssales
where i_color = 'saddle'
group by c_last_name
        ,c_first_name
        ,s_store_name
having sum(netpaid) > (select 0.05*avg(netpaid)
                           from ssales)
order by c_last_name
        ,c_first_name
        ,s_store_name
;
 
