Dormant merchants RFI List 1 rule 6
Fri Feb 24 2023 07:15:00 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=default; set hive.execution.engine=tez; -- mx had >= 90 days of inactivity betw any 2 consecutive txns SELECT A.receiveruser, A.submerchant_id, B.inactive_days, C.amt, C.active_days, C.sender_cnt, C.txn_count FROM -- active IN 2022, before 6 months period (SELECT DISTINCT receiveruser, submerchant_id FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN DATE_SUB('2023-01-02', 32+181+31) AND DATE_SUB('2023-01-02', 32+181+1) AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND receivertype = 'MERCHANT')A INNER JOIN -- inactive some time in next 6 months (SELECT receiveruser, submerchant_id, MAX(datediff (txn1,txn2)) as inactive_days FROM (Select receiveruser, submerchant_id, updated_date as txn1, lag (updated_date) over (partition by receiveruser order by updated_date) as txn2 FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN DATE_SUB('2023-01-02', 32+181) AND DATE_SUB('2023-01-02', 32+1) AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND receivertype = 'MERCHANT')X GROUP BY receiveruser, submerchant_id HAVING MAX(datediff (txn1,txn2)) > 100)B ON A.receiveruser = B.receiveruser AND A.submerchant_id = B.submerchant_id INNER JOIN -- active again in Jan 2023 (SELECT receiveruser, submerchant_id, count(distinct transaction_id) as txn_count, 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-02', 32) AND DATE_SUB('2023-01-02', 2) AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND receivertype = 'MERCHANT' GROUP BY receiveruser, submerchant_id HAVING amt > 1000000)C ON A.receiveruser = C.receiveruser AND A.submerchant_id = C.submerchant_id ORDER BY C.amt DESC, B.inactive_days -- SELECT '2023-01-02' AS run_date, -- DATE_SUB('2023-01-02', 32) as new_active_start_date, -- DATE_SUB('2023-01-02', 2) as new_active_end_date, -- DATE_SUB('2023-01-02', 32+181) as inactive_start_date, -- DATE_SUB('2023-01-02', 32+1) as inactive_end_date, -- DATE_SUB('2023-01-02', 32+181+31) as old_active_start_date, -- DATE_SUB('2023-01-02', 32+181+1) as old_active_end_date -- -- run_date 2023-01-02 -- -- new_active_start_date 2022-12-01 -- -- new_active_end_date 2022-12-31 -- -- inactive_start_date 2022-06-03 -- -- inactive_end_date 2022-11-30 -- -- old_active_start_date 2022-05-03 -- -- old_active_end_date 2022-06-02
Comments