SELECT B.receiveruser, B.no_of_txns, B.Active_days, B.last_txn_date, A.blacklisted_date FROM (SELECT merchant_id, blacklisted_date FROM edw_shared.merchant_store_dimension WHERE blacklisted_date between '2023-02-01' AND '2023-02-28' AND blacklisted = 1 AND merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') )A INNER JOIN (SELECT merchant_id FROM merchant_onboarding.merchants WHERE onboarded_at between '2022-11-01' and '2023-12-31' AND merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') )C ON A.merchant_id = C.merchant_id INNER JOIN ( SELECT receiveruser, COUNT (distinct transaction_id) as no_of_txns, COUNT(distinct updated_date) as Active_days, MAX(updated_date) as last_txn_date FROM fraud.transaction_details_v3 WHERE updated_date >= '2022-11-01' AND backend_errorcode = 'SUCCESS' AND receivertype = 'MERCHANT' and receiversubtype IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') Group by receiveruser )B ON C.merchant_id = B.receiveruser WHERE B.no_of_txns > 500 ORDER BY B.no_of_txns DESC