-- DORMANT MERCHANTS
SELECT A.merchant_id, A.onboarded_at, C.amt, C.active_days, C.sender_cnt FROM
(SELECT merchant_id, Date(onboarded_at) as onboarded_at FROM merchant_onboarding.merchants
WHERE year = 2022 AND month <= (month(DATE_SUB('2023-01-01', 106))) AND Date(onboarded_at) <= DATE_SUB('2023-01-01', 106))A
LEFT JOIN
(SELECT DISTINCT receiveruser FROM fraud.transaction_details_v3
WHERE updated_date BETWEEN DATE_SUB('2023-01-01', 105) AND DATE_SUB('2023-01-01', 16)
AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
AND receivertype = 'MERCHANT')B
ON A.merchant_id = B.receiveruser
INNER JOIN
(SELECT receiveruser, SUM(totaltransactionamount) as amt, COUNT(DISTINCT updated_date) as active_days, COUNT(DISTINCT senderuserid) AS sender_cnt
FROM fraud.transaction_details_v3
WHERE updated_date BETWEEN DATE_SUB('2023-01-01', 15) AND DATE_SUB('2023-01-01', 1)
AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
AND receivertype = 'MERCHANT'
GROUP BY receiveruser
HAVING amt > 500000)C
ON A.merchant_id = C.receiveruser
WHERE B.receiveruser IS NULL
ORDER BY amt DESC, sender_cnt DESC
-- SELECT DATE_SUB('2023-01-01', 106) AS onboarded_before
-- , DATE_SUB('2023-01-01', 105) AS inactive_start_date
-- , DATE_SUB('2023-01-01', 16) AS inactive_end_date
-- , DATE_SUB('2023-01-01', 15) as active_start_date
-- , DATE_SUB('2023-01-01', 1) as active_end_date
Comments