RFI 21
Wed Apr 26 2023 17:16:59 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name = fra_operations; set hive.execution.engine=tez; --RULE 21 SELECT DATE_SUB('2023-04-02',32) starting_date ,DATE_SUB('2023-04-02',2) ending_date ,senderuserid AS identifier ,active_days as active_days ,ratio_with_total AS value ,CONCAT('RFI21 ', red_flag) AS red_flag ,'monthly' as date_range ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'UPI' sub_issue_type ,CONCAT('debit_txns: ', debit_txns, ' , credit_txns: ', credit_txns) AS comment FROM (SELECT A.senderuserid, A.debit_txns, B.credit_txns, (A.debit_txns+B.credit_txns) as total_txns , IF(A.debit_txns > B.credit_txns, 'DEBIT', 'CREDIT') as red_flag ,(greatest (A.debit_txns, B.credit_txns) / (A.debit_txns+B.credit_txns)) as ratio_with_total , IF(A.debit_txns > B.credit_txns, A.active_days, B.active_days) as active_days FROM (SELECT senderuserid, COUNT (DISTINCT transaction_id) as debit_txns, COUNT(DISTINCT updated_date) as active_days FROM fraud.transaction_details_v3 WHERE updated_date between DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) AND backend_errorcode = 'SUCCESS' AND transfermode = 'PEER_TO_PEER' GROUP BY senderuserid)A INNER JOIN (SELECT receiveruser, COUNT (DISTINCT transaction_id) as credit_txns, count(distinct sender_vpa) sender_vpa, COUNT(DISTINCT updated_date) as active_days FROM fraud.transaction_details_v3 WHERE updated_date between DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) AND backend_errorcode = 'SUCCESS' AND transfermode = 'PEER_TO_PEER' GROUP BY receiveruser)B ON A.senderuserid = B.receiveruser WHERE (A.debit_txns+B.credit_txns) > 2000 AND (A.debit_txns/ (A.debit_txns+B.credit_txns) >0.9 OR B.credit_txns/ (A.debit_txns+B.credit_txns) >0.9) AND (A.debit_txns > 3000 OR (B.credit_txns>5000 AND sender_vpa>1000 )) GROUP BY A.senderuserid, A.debit_txns, B.credit_txns, A.active_days, B.active_days ORDER BY ratio_with_total desc)X
Comments