cardinality variables receiverside
Mon Jul 17 2023 11:11:54 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%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
Comments