RULE 20 edw.shared_merchant_store_domesnion

--high sales fraud ratio
SELECT A.receiveruser, A.txn_cnt, A.txn_amount, B.receiveruserid,B.fraud_txn_cnt,B.fraud_txn_amount
from 
(
(SELECT receiveruserid, count(distinct transaction_id) as fraud_txn_cnt, sum(fraud_amt_rs) as fraud_txn_amount
from fraud.mark_transaction_details_v2 
where date_tx between '2023-03-01' and '2023-03-31'  
group by receiveruserid)B
INNER join 
(SELECT receiveruser, count(distinct transaction_id) as txn_cnt, sum(totaltransactionamount) as txn_amount
from fraud.transaction_details_v3
where updated_date between '2023-03-01' and '2023-03-31' and  receiversubtype In ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR')
group by receiveruser)A
On  B.receiveruserid = A.receiveruser)
where (B.fraud_txn_amount > 10000 or A.txn_cnt/B.fraud_txn_cnt > 0.5) and A.txn_amount > 2000000
order by B.fraud_txn_amount desc