senderside

%jdbc(hive)
set tez.queue.name=bullseye;
set hive.execution.engine=tez;

-- MASTER TABLE DAILY INSERT
-- INSERT INTO fraud.aml_daily_debit_agg_master 

SELECT senderuserid
, updated_date
, off_hr_sndr_txn
, off_hr_sndr_amt
, sndr_card_txn_cnt
, sndr_card_txn_amt
, wallet_txn_suc
, egv_redeem_txn_cnt
, egv_redeem_txn_amt
, egv_linked
, upi_fail_txn
, upi_suc_txn
, card_fail_txn
, card_suc_txn
FROM
    (SELECT senderuserid, updated_date
    , COALESCE(count(case when upi_flag = true AND hour(transaction_time) in (22,23,00,01,02,03,04,05,06,07) then transaction_id else NULL end),0) as off_hr_sndr_txn
    , COALESCE(sum(case when upi_flag = true AND hour(transaction_time) in (22,23,00,01,02,03,04,05,06,07) then totaltransactionamount else 0 end),0) as off_hr_sndr_amt
    , COALESCE(count(case when card_flag = TRUE THEN transaction_id else NULL end),0) as sndr_card_txn_cnt
    , COALESCE(sum(case when card_flag = TRUE THEN totaltransactionamount else 0 end),0) as sndr_card_txn_amt
    , COALESCE(sum(case when receivertype in ('MERCHANT','USER') and wallet_flag = TRUE then transaction_id else NULL end),0) AS wallet_txn_suc 
    , COALESCE(COUNT(case when receivertype = 'MERCHANT' AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false THEN transaction_id else NULL end),0) as egv_redeem_txn_cnt
    , COALESCE(sum(case when receivertype = 'MERCHANT' AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false THEN totaltransactionamount else 0 end),0) as egv_redeem_txn_amt
    , COALESCE(COUNT(CASE WHEN upi_flag = TRUE AND errorcode <> 'SUCCESS' THEN transaction_id ELSE NULL END), 0) AS upi_fail_txn
    , COALESCE(COUNT(CASE WHEN upi_flag = TRUE AND errorcode = 'SUCCESS' THEN transaction_id ELSE NULL END), 0) AS upi_suc_txn
    , COALESCE(COUNT(CASE WHEN card_flag = TRUE AND errorcode <> 'SUCCESS' THEN transaction_id ELSE NULL END), 0) AS card_fail_txn
    , COALESCE(COUNT(CASE WHEN card_flag = TRUE AND errorcode = 'SUCCESS' THEN transaction_id ELSE NULL END), 0) AS card_suc_txn
    FROM fraud.transaction_details_v3 
    WHERE updated_date BETWEEN DATE_SUB('{{ ds }}', 90) AND DATE_SUB('{{ ds }}', 1)
    AND sendertype = 'INTERNAL_USER'
    AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
    GROUP BY senderuserid, updated_date)A
LEFT JOIN
    (SELECT user_id, last_link_transaction_date, COALESCE(COUNT(global_card_id), 0) as egv_linked 
    FROM egv.gift_cards 
    WHERE last_link_transaction_date BETWEEN DATE_SUB('{{ ds }}', 90) AND DATE_SUB('{{ ds }}', 1)
    AND card_type = 'GIFT_VOUCHER' AND gift_card_status	= 'ACTIVATED' AND card_link_state = 'LINKED'
    AND merchant_group_id = 'phonepeGroup' AND tenant_id = 'PHONEPE'
    GROUP BY user_id, last_link_transaction_date)B
ON A.senderuserid = B.user_id AND A.updated_date = B.last_link_transaction_date

-- -- -----------------------------------------------------------------------------------------------------------------------
-- -- CALCULATING AGG FROM MASTER TABLE
-- SELECT A.senderuserid
-- , 
--     (
--     SELECT senderuserid
--     -- 7d data
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then off_hr_sndr_txn ELSE 0 END) AS off_hr_sndr_txn_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then off_hr_sndr_amt ELSE 0 END) AS off_hr_sndr_amt_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then sndr_card_txn_cnt ELSE 0 END) AS sndr_card_txn_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then sndr_card_txn_amt ELSE 0 END) AS sndr_card_amt_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then wallet_txn_suc ELSE 0 END) AS sndr_wallet_txn_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then egv_redeem_txn_cnt ELSE 0 END) AS sndr_egv_txn_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then egv_redeem_txn_amt ELSE 0 END) AS sndr_egv_amt_7d
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then egv_linked ELSE 0 END) AS sndr_egv_linked_7d
    
--     -- 1m data
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then off_hr_sndr_txn ELSE 0 END) AS off_hr_sndr_txn_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then off_hr_sndr_amt ELSE 0 END) AS off_hr_sndr_amt_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then sndr_card_txn_cnt ELSE 0 END) AS sndr_card_txn_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then sndr_card_txn_amt ELSE 0 END) AS sndr_card_amt_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then wallet_txn_suc ELSE 0 END) AS sndr_wallet_txn_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then then egv_redeem_txn_cnt ELSE 0 END) AS sndr_egv_txn_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then then egv_redeem_txn_amt ELSE 0 END) AS sndr_egv_amt_1m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then egv_linked ELSE 0 END) AS sndr_egv_linked_1m

--     -- 2m data
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then off_hr_sndr_txn ELSE 0 END) AS off_hr_sndr_txn_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then off_hr_sndr_amt ELSE 0 END) AS off_hr_sndr_amt_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then sndr_card_txn_cnt ELSE 0 END) AS sndr_card_txn_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then sndr_card_txn_amt ELSE 0 END) AS sndr_card_amt_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then wallet_txn_suc ELSE 0 END) AS sndr_wallet_txn_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then egv_redeem_txn_cnt ELSE 0 END) AS sndr_egv_txn_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then egv_redeem_txn_amt ELSE 0 END) AS sndr_egv_amt_2m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then egv_linked ELSE 0 END) AS sndr_egv_linked_2m
    
--     -- 3m data
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then off_hr_sndr_txn ELSE 0 END) AS off_hr_sndr_txn_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then off_hr_sndr_amt ELSE 0 END) AS off_hr_sndr_amt_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then sndr_card_txn_cnt ELSE 0 END) AS sndr_card_txn_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then sndr_card_txn_amt ELSE 0 END) AS sndr_card_amt_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then wallet_txn_suc ELSE 0 END) AS sndr_wallet_txn_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then egv_redeem_txn_cnt ELSE 0 END) AS sndr_egv_txn_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then egv_redeem_txn_amt ELSE 0 END) AS sndr_egv_amt_3m
--     , SUM(CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then egv_linked ELSE 0 END) AS sndr_egv_linked_3m
    
--     FROM fraud.aml_daily_debit_agg_master
--     WHERE updated_date >= date_sub('{{ ds }}',90)
--     GROUP BY senderuserid
--     )A
-- LEFT JOIN
--     (--QUERY FOR DISTINCTS -- TO BE JOINED WITH FINAL AGGREGATE TABLE
--     SELECT senderuserid,
--     -- 7d data
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then updated_date else NULL end) as active_days_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as mx_rcvr_cnt_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then reciver_vpa else NULL end) as rcvr_cnt_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) then sender_district else NULL end) as sender_district_cnt_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as senderbankacc_cnt_7d,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND card_flag = TRUE then masked_card_number ELSE 0 END) AS sndr_card_cnt_7d,
--     -- COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND card_flag = TRUE then accountingproviderid ELSE 0 END) AS sndr_card_cnt_7d,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND card_flag = TRUE AND receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') then IF(masked_card_number IS NULL, senderbankaccountsha, masked_card_number) ELSE 0 END) AS wallet_topup_inst_cnt_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then updated_date else NULL end) as wallet_redeem_active_days_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then reciver_vpa else NULL end) as wallet_redeem_mx_cnt_7d,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',7) and date_sub('{{ ds }}',1) AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false then reciver_vpa else NULL end) as egv_redeem_mx_cnt_7d,
    
--     -- 1m data
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then updated_date else NULL end) as active_days_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as mx_rcvr_cnt_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then reciver_vpa else NULL end) as rcvr_cnt_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) then sender_district else NULL end) as sender_district_cnt_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as senderbankacc_cnt_1m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND card_flag = TRUE then masked_card_number ELSE 0 END) AS sndr_card_cnt_1m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND card_flag = TRUE AND receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') then IF(masked_card_number IS NULL, senderbankaccountsha, masked_card_number) ELSE 0 END) AS wallet_topup_inst_cnt_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then updated_date else NULL end) as wallet_redeem_active_days_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then reciver_vpa else NULL end) as wallet_redeem_mx_cnt_1m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',30) and date_sub('{{ ds }}',1) AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false then reciver_vpa else NULL end) as egv_redeem_mx_cnt_1m,
    
--     -- 2m data 
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then updated_date else NULL end) as active_days_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as mx_rcvr_cnt_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then reciver_vpa else NULL end) as rcvr_cnt_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) then sender_district else NULL end) as sender_district_cnt_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as senderbankacc_cnt_2m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND card_flag = TRUE then masked_card_number ELSE 0 END) AS sndr_card_cnt_2m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND card_flag = TRUE AND receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') then IF(masked_card_number IS NULL, senderbankaccountsha, masked_card_number) ELSE 0 END) AS wallet_topup_inst_cnt_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then updated_date else NULL end) as wallet_redeem_active_days_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then reciver_vpa else NULL end) as wallet_redeem_mx_cnt_2m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',60) and date_sub('{{ ds }}',1) AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false then reciver_vpa else NULL end) as egv_redeem_mx_cnt_2m,
    
--     -- 3m data
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then updated_date else NULL end) as active_days_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as mx_rcvr_cnt_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then reciver_vpa else NULL end) as rcvr_cnt_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) then sender_district else NULL end) as sender_district_cnt_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as senderbankacc_cnt_3m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND card_flag = TRUE then masked_card_number ELSE 0 END) AS sndr_card_cnt_3m,
--     COUNT(DISTINCT CASE WHEN updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND card_flag = TRUE AND receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') then IF(masked_card_number IS NULL, senderbankaccountsha, masked_card_number) ELSE 0 END) AS wallet_topup_inst_cnt_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then updated_date else NULL end) as wallet_redeem_active_days_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND wallet_flag = TRUE then reciver_vpa else NULL end) as wallet_redeem_mx_cnt_3m,
--     COUNT(DISTINCT case when updated_date between date_sub('{{ ds }}',90) and date_sub('{{ ds }}',1) AND upi_flag = false AND card_flag = false AND wallet_flag = false AND freecharge_flag = false AND jio_flag = false AND airtel_flag = false then reciver_vpa else NULL end) as egv_redeem_mx_cnt_3m,
    
--     FROM fraud.transaction_details_v3
--     WHERE updated_date >= date_sub('{{ ds }}',90)
--     AND sendertype = 'INTERNAL_USER'
--     and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
--     GROUP BY senderuserid
--     )B
-- ON A.senderuserid = B.senderuserid