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