Rule 2
Tue Apr 25 2023 06:37:23 GMT+0000 (Coordinated Universal Time)
Saved by @saumyatiwari
--RULE 2 Sudden increase in value of cumulative transactions in a defined time span for a Merchant SELECT A.receiveruser, A.previous_txn_cnt,B.current_txn_cnt, ((B.current_amount_tol - A.previous_amount_tol)/A.previous_amount_tol) as percent_increase_amount,A.previous_amount_tol,B.current_amount_tol, A.previous_active_days, 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) < '2022-09-30'))C LEFT JOIN (SELECT receiveruser, count(distinct transaction_id) as previous_txn_cnt , sum(totaltransactionamount) as previous_amount_tol , count(distinct updated_date) as previous_active_days from fraud.transaction_details_v3 where updated_date between '2022-12-01' and '2022-12-31' 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) >200000)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 '2023-01-01' and '2023-01-31' 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) >200000)B ON A.receiveruser = B.receiveruser where ((B.current_amount_tol - A.previous_amount_tol)/A.previous_amount_tol) > 0.5 and ((B.current_txn_cnt-A.previous_txn_cnt)/B.current_txn_cnt) >0.5
Comments