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;



 select i_brand_id brand_id, i_brand brand, i_manufact_id, i_manufact,
 	sum(ss_ext_sales_price) ext_price
 from date_dim, store_sales, item,customer,customer_address,store
 where d_date_sk = ss_sold_date_sk
   and ss_item_sk = i_item_sk
   and i_manager_id=8
   and d_moy=11
   and d_year=1998
   and ss_customer_sk = c_customer_sk 
   and c_current_addr_sk = ca_address_sk
   and substr(ca_zip,1,5) <> substr(s_zip,1,5) 
   and ss_store_sk = s_store_sk 
 group by i_brand
      ,i_brand_id
      ,i_manufact_id
      ,i_manufact
 order by ext_price desc
         ,i_brand
         ,i_brand_id
         ,i_manufact_id
         ,i_manufact
limit 100 ;
