RFI 3
Mon May 01 2023 20:11:35 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name = fra_analytics; set hive.execution.engine=tez; -- RFI 3 select DATE_SUB('2023-04-02',32) starting_date ,DATE_SUB('2023-04-02',2) ending_date ,receiveruser AS identifier ,current_active_days as active_days ,current_txn_cnt AS value ,'RFI3' AS red_flag ,'monthly' as date_range ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'UPI' sub_issue_type ,CONCAT('previous_txn_cnt: ', previous_txn_cnt, ' , percent_increase_count: ', percent_increase_count, ' , previous_amount_tol: ', previous_amount_tol, ' , current_amount_tol: ', current_amount_tol) AS comment from (SELECT A.receiveruser, A.previous_txn_cnt, B.current_txn_cnt , ((B.current_txn_cnt - A.previous_txn_cnt)/A.previous_txn_cnt) as percent_increase_count , A.previous_amount_tol, B.current_amount_tol, B.current_active_days FROM (SELECT merchant_id, onboarded_at from merchant_onboarding.merchants where merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') and date(onboarded_at) < DATE_SUB('2023-04-02',33+30+60))C LEFT JOIN (SELECT receiveruser, count(distinct transaction_id) as previous_txn_cnt, sum(totaltransactionamount) as previous_amount_tol from fraud.transaction_details_v3 where updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) and receivertype = 'MERCHANT' and receiversubtype In ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED' group by receiveruser having sum(totaltransactionamount) >100000)A ON C.merchant_id = A.receiveruser LEFT JOIN (SELECT receiveruser, count(distinct transaction_id) as current_txn_cnt , sum(totaltransactionamount) as current_amount_tol , count(distinct updated_date) as current_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 receivertype = 'MERCHANT' and receiversubtype In ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED' group by receiveruser having sum(totaltransactionamount) >100000)B ON A.receiveruser = B.receiveruser where ((B.current_txn_cnt - A.previous_txn_cnt)/A.previous_txn_cnt) >=20)X
Comments