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 case when (select count(*)
                  from store_sales
                  where ss_quantity between 1 and 20) > 74129
            then (select avg(ss_ext_discount_amt)
                  from store_sales
                  where ss_quantity between 1 and 20)
            else (select avg(ss_net_paid)
                  from store_sales
                  where ss_quantity between 1 and 20) end bucket1 ,
       case when (select count(*)
                  from store_sales
                  where ss_quantity between 21 and 40) > 122840
            then (select avg(ss_ext_discount_amt)
                  from store_sales
                  where ss_quantity between 21 and 40)
            else (select avg(ss_net_paid)
                  from store_sales
                  where ss_quantity between 21 and 40) end bucket2,
       case when (select count(*)
                  from store_sales
                  where ss_quantity between 41 and 60) > 56580
            then (select avg(ss_ext_discount_amt)
                  from store_sales
                  where ss_quantity between 41 and 60)
            else (select avg(ss_net_paid)
                  from store_sales
                  where ss_quantity between 41 and 60) end bucket3,
       case when (select count(*)
                  from store_sales
                  where ss_quantity between 61 and 80) > 10097
            then (select avg(ss_ext_discount_amt)
                  from store_sales
                  where ss_quantity between 61 and 80)
            else (select avg(ss_net_paid)
                  from store_sales
                  where ss_quantity between 61 and 80) end bucket4,
       case when (select count(*)
                  from store_sales
                  where ss_quantity between 81 and 100) > 165306
            then (select avg(ss_ext_discount_amt)
                  from store_sales
                  where ss_quantity between 81 and 100)
            else (select avg(ss_net_paid)
                  from store_sales
                  where ss_quantity between 81 and 100) end bucket5
from reason
where r_reason_sk = 1
;

