RULE 8
Mon May 01 2023 17:05:38 GMT+0000 (Coordinated Universal Time)
Saved by
@saumyatiwari
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
content_copyCOPY
Comments