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