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 customer_total_return as
 (select cr_returning_customer_sk as ctr_customer_sk
        ,ca_state as ctr_state, 
 	sum(cr_return_amt_inc_tax) as ctr_total_return
 from catalog_returns
     ,date_dim
     ,customer_address
 where cr_returned_date_sk = d_date_sk 
   and d_year =2000
   and cr_returning_addr_sk = ca_address_sk 
 group by cr_returning_customer_sk
         ,ca_state )
 select c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
                  ,ca_location_type,ctr_total_return
 from customer_total_return ctr1
     ,customer_address
     ,customer
 where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
 			  from customer_total_return ctr2 
                  	  where ctr1.ctr_state = ctr2.ctr_state)
       and ca_address_sk = c_current_addr_sk
       and ca_state = 'GA'
       and ctr1.ctr_customer_sk = c_customer_sk
 order by c_customer_id,c_salutation,c_first_name,c_last_name,ca_street_number,ca_street_name
                   ,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset
                  ,ca_location_type,ctr_total_return
 limit 100;
 
