with user_merchant_txn as ( SELECT receiveruser, senderuserid, count(distinct transaction_id) as total_send_txns, SUM(totaltransactionamount) as total_send_amt from fraud.transaction_details_v3 where updated_date between '2023-03-01' and '2023-03-31' and pay_transaction_status = 'COMPLETED' and sendertype = 'INTERNAL_USER' and workflowtype IN ('CONSUMER_TO_CONSUMER','CONSUMER_TO_CONSUMER_V2') and transfermode in ('PEER_TO_PEER') and receiveruser<> senderuserid GROUP BY receiveruser,senderuserid ), user_txn as ( select receiveruser , count(distinct transaction_id) txns_all_30d , SUM(totaltransactionamount) as total_send_amt_all_30d from fraud.transaction_details_v3 where updated_date between '2023-03-01' and '2023-03-31' and pay_transaction_status = 'COMPLETED' and transfermode in ('PEER_TO_PEER') and workflowtype IN ('CONSUMER_TO_CONSUMER','CONSUMER_TO_CONSUMER_V2') group by receiveruser ) select a.senderuserid , a.receiveruser, total_send_txns , txns_all_30d,total_send_amt_all_30d,total_send_amt from user_merchant_txn a left join user_txn b on a.receiveruser = b.receiveruser where total_send_amt >5000 and total_send_amt/total_send_amt_all_30d > 0.6 limit 1000
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter