SET hive.execution.engine =tez; SET mapreduce.map.memory.mb=24576; drop table if exists frs_analytics_hive.dfr_distinctNot; Create table frs_analytics_hive.dfr_distinctNot as with owneditems as (select mdse_item_i, mdse_item_ref_i, mdse_dept_ref_i, mdse_clas_ref_i, mdse_item_desc_t, mdse_brnd_type_lbl_t, mdse_brnd_lbl_t, brnd_styp_c from prd_sho_item.mdse_item_dim where mdse_brnd_type_lbl_t = 'TGT-OWNED' and mdse_grp_ref_i = 10), FNBItems as (select mdse_item_i, mdse_item_ref_i, mdse_dept_ref_i, mdse_clas_ref_i, mdse_item_desc_t, mdse_brnd_type_lbl_t, brnd_styp_c from prd_sho_item.mdse_item_dim where mdse_grp_ref_i = 10), MilkAndEggs as (select mdse_item_i, mdse_item_ref_i, mdse_dept_ref_i, mdse_clas_ref_i, mdse_item_desc_t, mdse_brnd_type_lbl_t, brnd_styp_c from prd_sho_item.mdse_item_dim where mdse_grp_ref_i = 10 and ((mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 1) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 3) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 6) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 8) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 9) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 2 and mdse_sbcl_ref_i = 10) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 3 and mdse_sbcl_ref_i = 3) or (mdse_dept_ref_i = 284 and Mdse_clas_ref_i = 3 and mdse_sbcl_ref_i = 7))), ownedFNBdetail as (select a.gst_ref_i, b.tran_i, b.mdse_item_i, sum(ext_sls_a) as sales, sum(case when d.mdse_item_i is not null then 1 else 0 end) as MilkOrEggs, sum(case when d.mdse_item_i is not null then ext_sls_a else 0 end) as MilkOrEggssales, sum(case when mdse_brnd_lbl_t = 'Archer Farms' then ext_sls_a else 0 end) as AFSales, sum(case when mdse_brnd_lbl_t = 'market pantry' then ext_sls_a else 0 end) as MPSales, sum(case when mdse_brnd_lbl_t = 'Simply Balanced' then ext_sls_a else 0 end) as SBSales, sum(case when mdse_brnd_lbl_t = 'Archer Farms' then 1 else 0 end) as AFItem, sum(case when mdse_brnd_lbl_t = 'market pantry' then 1 else 0 end) as MPItem, sum(case when mdse_brnd_lbl_t = 'Simply Balanced' then 1 else 0 end) as SBItem from prd_gst_fnd.mchan_tran_asct a inner join prd_sls_fnd.sls_litm b on a.agi_tran_i = b.tran_i inner join owneditems c on b.mdse_item_i = c.mdse_item_i left join MilkAndEggs d on b.mdse_item_i = d.mdse_item_i where gst_tran_rnk_i = 1 and sls_d between date_add('2018-04-30', -364) and date_add('2018-04-30', 0) and a.gst_ref_i = 100000000938 group by a.gst_ref_i, b.tran_i, b.mdse_item_i), ownedFNBguests as (select gst_ref_i, count(distinct tran_i) as unique_owned_FNB_Trans, count(distinct mdse_item_i) as unique_owned_FNB_Items, sum(sales) as owned_FNB_Sales, sum(MilkOrEggs) as MilkOrEggs, sum(MilkOrEggssales) as MilkOrEggssales, sum(AFSales) as AFSales, sum(MPSales) as MPSales, sum(SBSales) as SBSales, sum(AFItem) as AFItems, sum(MPItem) as MPItems, sum(SBItem) as SBItems from ownedFNBdetail a group by gst_ref_i) select * from ownedFNBguests;