RULE 15

SELECT A.receiveruser, A.active_days_previous, A.amount_total_previous, A.txn_cnt_previous, A.sender_vpa_cnt_previous, B.active_days_current,B.amount_total_current, B.txn_cnt_current,B.sender_vpa_cnt_current, (A.amount_total_previous-B.amount_total_current)/B.amount_total_current as percent_dip_in_amount
FROM
(
    (SELECT receiveruser, count(distinct updated_date) as active_days_previous, sum(totaltransactionamount) as amount_total_previous, count(distinct transaction_id) as txn_cnt_previous, count(distinct senderuserid) as sender_vpa_cnt_previous
    from fraud.transaction_details_v3
    where updated_date between '2023-01-01' and '2023-01-31' and receivertype = 'MERCHANT' and  receiversubtype In ('P2P_MERCHANT','P2M_LIMITED') and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'  and mcc not in ( '5944', '5541')
    group by receiveruser
    Having txn_cnt_previous > 100)A
INNER JOIN
    (SELECT receiveruser, count(distinct updated_date) as active_days_current, sum(totaltransactionamount) as amount_total_current, count(distinct transaction_id) as txn_cnt_current, count(distinct senderuserid) as sender_vpa_cnt_current
    from fraud.transaction_details_v3
    where updated_date between '2023-02-01' and '2023-02-28' and receivertype = 'MERCHANT' and  receiversubtype In ('P2P_MERCHANT','P2M_LIMITED') and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'   and mcc not in ( '5944', '5541')
    group by receiveruser)B
    ON A.receiveruser = B.receiveruser
  INNER JOIN
(select merchant_id, count(distinct store_id) as store_cnt
from fraud.offline_merchant_profile_v2
group by merchant_id
having store_cnt <= 2)C
ON B.receiveruser = C.merchant_id
)
 where (( A.sender_vpa_cnt_previous - B.sender_vpa_cnt_current )>500 ) and (A.amount_total_previous-B.amount_total_current) > 2000000