Dormant merchants RFI List 1 rule 6

%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