Cardinality variables senderside

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

-- CARDINALITY VARIBALES SENDERSIDE -- 18 mins
SELECT senderuserid,
-- 7d data
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) then updated_date else NULL end) as sndr_upi_active_days_7d,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as sndr_upi_mx_cnt_7d,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) then reciver_vpa else NULL end) as sndr_upi_mx_vpa_cnt_7d,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) then sender_district else NULL end) as sndr_upi_district_cnt_7d,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as sndr_upi_bankacc_cnt_7d,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',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('2023-07-15',7) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then receiveruser ELSE 0 END) AS sndr_card_mx_cnt_7d,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then accountingproviderid ELSE 0 END) AS sndr_card_sub_mx_cnt_7d,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',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('2023-07-15',7) and date_sub('2023-07-15',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('2023-07-15',7) and date_sub('2023-07-15',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('2023-07-15',7) and date_sub('2023-07-15',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('2023-07-15',30) and date_sub('2023-07-15',1) then updated_date else NULL end) as sndr_upi_active_days_1m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as sndr_upi_mx_cnt_1m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) then reciver_vpa else NULL end) as sndr_upi_mx_vpa_cnt_1m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) then sender_district else NULL end) as sndr_upi_district_cnt_1m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as sndr_upi_bankacc_cnt_1m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',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('2023-07-15',30) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then receiveruser ELSE 0 END) AS sndr_card_mx_cnt_1m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then accountingproviderid ELSE 0 END) AS sndr_card_sub_mx_cnt_1m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',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('2023-07-15',30) and date_sub('2023-07-15',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('2023-07-15',30) and date_sub('2023-07-15',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('2023-07-15',30) and date_sub('2023-07-15',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,


-- 3m data
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then updated_date else NULL end) as sndr_upi_active_days_3m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) AND receivertype = 'MERCHANT' then receiveruser else NULL end) as sndr_upi_mx_cnt_3m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then reciver_vpa else NULL end) as sndr_upi_mx_vpa_cnt_3m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then sender_district else NULL end) as sndr_upi_district_cnt_3m,
COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) AND upi_flag = TRUE then senderbankaccountsha else NULL end) as sndr_upi_bankacc_cnt_3m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',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('2023-07-15',90) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then receiveruser ELSE 0 END) AS sndr_card_mx_cnt_3m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) AND card_flag = TRUE AND receivertype = 'MERCHANT' then accountingproviderid ELSE 0 END) AS sndr_card_sub_mx_cnt_3m,
COUNT(DISTINCT CASE WHEN updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',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('2023-07-15',90) and date_sub('2023-07-15',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('2023-07-15',90) and date_sub('2023-07-15',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('2023-07-15',90) and date_sub('2023-07-15',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('2023-07-15',90)
AND sendertype = 'INTERNAL_USER'
AND senderuserid IN (SELECT DISTINCT identifier FROM fraud.aml_freshdesk_month WHERE run_date = '2023-07-02' AND identifier LIKE 'U%')
and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS' AND backend_errorcode = 'SUCCESS'
GROUP BY senderuserid