Rule 2

--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