RULE 3

PHOTO

Mon May 01 2023 17:08:37 GMT+0000 (Coordinated Universal Time)

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, 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-11-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 '2023-02-01' and '2023-02-28' 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 '2023-03-01' and '2023-03-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) >100000)B
ON A.receiveruser = B.receiveruser )
where ((B.current_txn_cnt - A.previous_txn_cnt)/A.previous_txn_cnt) >=20

COPY