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