%jdbc(hive) set tez.queue.name=phonepe_verified; set hive.execution.engine=tez; --RFI 20 SELECT DATE_SUB('2023-05-02',32) starting_date ,DATE_SUB('2023-05-02',2) ending_date ,receiveruser AS identifier ,active_days as active_days ,perc AS value ,'RFI20' AS red_flag ,'monthly' as date_range ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'Merchant' issue_type ,'UPI' sub_issue_type ,CONCAT('fraud_txns: ', fraud_txns, ' , fraud_amount: ', fraud_amount, ' , success_txns: ', success_txns, ' , success_amount: ', success_amount) AS comment FROM (SELECT receiveruser, fraud_txns, fraud_amount, success_txns, success_amount, (fraud_amount/success_amount) as perc, active_days FROM (SELECT receiveruser, count(distinct transaction_id) as success_txns, sum(totaltransactionamount) as success_amount, COUNT(DISTINCT updated_date) as active_days from fraud.transaction_details_v3 where updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) AND workflowtype IN ('CONSUMER_TO_MERCHANT' , 'EXTERNAL_TO_MERCHANT') and (receiversubtype In ('OFFLINE_UNORGANISED','P2P_MERCHANT','P2M_LIMITED') or origination_mode = 'B2B_PG') AND backend_errorcode = 'SUCCESS' AND pay_transaction_status= 'COMPLETED' group by receiveruser)A INNER JOIN (SELECT receiveruserid, COUNT(DISTINCT transaction_id) as fraud_txns, SUM(fraud_amt_rs) as fraud_amount from fraud.mark_transaction_details_v2 where date_tx between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) GROUP BY receiveruserid)B ON A.receiveruser = B.receiveruserid where (fraud_amount/success_amount) > 0.5 and fraud_txns > 3 order by fraud_amount desc)X