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

-- CARDINALITY VARIABLES RECEIVERSIDE -- 19 mins
SELECT receiveruser
, 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 rcvr_upi_active_days_7d
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) then sender_vpa else NULL end) as rcvr_upi_sndr_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 rcvr_upi_sndr_district_cnt_7d
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',7) and date_sub('2023-07-15',1) then receiver_account_number else NULL end) as rcvr_upi_bankacc_cnt_7d

, 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 rcvr_upi_active_days_1m
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',30) and date_sub('2023-07-15',1) then sender_vpa else NULL end) as rcvr_upi_sndr_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 rcvr_upi_sndr_district_cnt_1m
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then receiver_account_number else NULL end) as rcvr_upi_bankacc_cnt_1m

, 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 rcvr_upi_active_days_3m
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then sender_vpa else NULL end) as rcvr_upi_sndr_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 rcvr_upi_sndr_district_cnt_3m
, COUNT(DISTINCT case when updated_date between date_sub('2023-07-15',90) and date_sub('2023-07-15',1) then receiver_account_number else NULL end) as rcvr_upi_bankacc_cnt_3m

FROM fraud.transaction_details_v3
WHERE updated_date >= date_sub('2023-07-15',90)
AND receivertype = 'INTERNAL_USER' AND upi_flag = TRUE
AND receiveruser 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 receiveruser