WITH TabA as (SELECT cid, NUM_PURCHASES from MyTable where dt>= '20161001' and dt <= '20181001'),
TabB as (SELECT cid from MyTable where dt='20181001')
    SELECT TabA.cid as ID,
    SUM(TabA.NUM_PURCHASES) as total_p
    FROM TabA LEFT SEMI JOIN TabB on (TabB.cid = TabA.cid) GROUP BY TabA.cid;

set hive.auto.convert.join=true;
SET hive.variable.substitute.depth=150;
set hive.auto.convert.join=true;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.skewjoin=true;
set hive.enforce.bucketing = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.exec.parallel=true;
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.vectorized.execution.reduce.groupby.enabled = true;
set hive.cbo.enable=true;
SET mapred.child.java.opts=-Xmx4G -XX:+UseConcMarkSweepGC  -XX:-UseGCOverheadLimit;
set mapreduce.map.memory.mb=9216;
set mapreduce.reduce.memory.mb=9216;