UNNATI PPI CASE 1

SELECT A.wallet_id
, B.deactivated_on
, B.kyc
, DATE(DATE(IF((D.otp_date IS NULL) OR (A.last_txn_date > D.otp_date), A.last_txn_date, D.otp_date)) + 365 DAY) AS last_txn_exceeded_365_days_on
, D.otp_date
, SUM(C.amt) as sum_txn_amt_during_period
FROM
    (SELECT DISTINCT wallet_id, last_txn_id, last_txn_date FROM 
        -- case 1 user is completely inactive after Apr 30, 2021
        (SELECT wallet_id, MAX(txn_id) as last_txn_id, MAX(DATE(txn_time)) as last_txn_date
        FROM wallet.transaction_master
        WHERE year IN (2021, 2022) AND Date(txn_time) <= '2022-04-30'
        AND category = 'ORDER' AND txn_type = 'DEBIT' 
        AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
        GROUP BY wallet_id
        HAVING last_txn_date <= '2021-04-30')A
    union
        -- should have been inactive after 365 days of inactivity, still transacted
        SELECT DISTINCT wallet_id, txn_id1 as last_txn_id, DATE(txn_time1) as last_txn_date FROM
            (SELECT DISTINCT wallet_id, txn_id as txn_id1, txn_time as txn_time1
            , LEAD (txn_id, 1) OVER (PARTITION BY wallet_id ORDER BY txn_time) AS txn_id2
            , LEAD (txn_time, 1) OVER (PARTITION BY wallet_id ORDER BY txn_time) AS txn_time2
            FROM wallet.transaction_master
            WHERE year IN (2021, 2022) AND Date(txn_time) <= '2022-04-30'
            AND category = 'ORDER' AND txn_type = 'DEBIT' 
            AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS')B
        WHERE DATEDIFF(txn_time2, txn_time1) >= 365 AND DATE(txn_time2) BETWEEN '2021-04-01' AND '2022-03-31'
    )A
INNER JOIN
    (SELECT DISTINCT wallet_id, old_kyc_type AS kyc, Date(created_at) AS deactivated_on FROM wallet.wallet_audit
    WHERE year IN (2021, 2022)
    AND old_wallet_state <> new_wallet_state AND new_wallet_state = 'INACTIVE'
    AND old_kyc_type LIKE 'MIN_KYC%'
    AND Date(created_at) >= '2021-04-01')B
ON A.wallet_id = B.wallet_id
LEFT JOIN
    (SELECT wallet_id, Date(txn_time) as txn_date, SUM(amount/100) as amt
    FROM wallet.transaction_master
    WHERE year IN (2021, 2022) 
    AND category = 'ORDER' AND txn_type = 'DEBIT' 
    AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS'
    GROUP BY wallet_id, Date(txn_time))C
ON A.wallet_id = C.wallet_id AND C.txn_date > last_txn_date AND C.txn_date < deactivated_on
LEFT JOIN
    (SELECT DISTINCT wallet_id, Date(created_at) as otp_date
    FROM wallet.wallet_hurdle_details
    WHERE year IN (2021, 2022)
    AND action = 'REVERT_WALLET_INACTIVE_STATE' AND hurdle_type = 'OTP')D
ON A.wallet_id = D.wallet_id
WHERE B.deactivated_on > DATE(DATE(IF((D.otp_date IS NULL) OR (A.last_txn_date > D.otp_date), A.last_txn_date, D.otp_date)) + 365 DAY)
GROUP BY A.wallet_id, B.deactivated_on, B.kyc, A.last_txn_date, D.otp_date