%jdbc(hive)
set tez.queue.name=default;
set hive.execution.engine=tez;

-- -- WALLETS TO BE DEACTIVATED -- old query
-- SELECT COUNT(DISTINCT A.wallet_id) as walletsDeactivated FROM
--     (SELECT DISTINCT wallet_id FROM wallet.wallet
--     WHERE Date(created_at) <= '2021-03-01'
--     AND is_blocked = 0 AND wallet_state = 'ACTIVATED' AND kyc_type LIKE 'MIN_KYC%')A
-- LEFT JOIN
--     (SELECT DISTINCT wallet_id FROM wallet.transaction_master
--     WHERE year IN (2021, 2022) AND Date(txn_time) BETWEEN '2021-03-01' AND '2022-01-15'
--     AND txn_type = 'DEBIT' 
--     AND txn_state = 'SUCCESS' AND response_code= 'SUCCESS')B
-- ON A.wallet_id = B.wallet_id 
-- WHERE B.wallet_id IS NULL


-- -- WALLETS TO BE DEACTIVATED -- new query --134814
-- SELECT COUNT(DISTINCT A.wallet_id) AS walletsDeactivated FROM
--     (SELECT DISTINCT wallet_id FROM wallet.wallet
--     WHERE Date(created_at) <= '2021-03-01'
--     AND is_blocked = 0 AND wallet_state = 'ACTIVATED' AND kyc_type LIKE 'MIN_KYC%')A
-- 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'
--     AND Date('2022-01-15') > Date(Date(created_at) + 365 DAY))B
-- ON A.wallet_id = B.wallet_id
-- LEFT JOIN
--     (SELECT wallet_id, MAX(Date(txn_time)) AS last_txn_dt FROM wallet.transaction_master
--     WHERE year IN (2021, 2022) --AND Date(txn_time) BETWEEN '2021-03-01' AND '2022-01-15'
--     AND txn_type = 'DEBIT' 
--     AND txn_state = 'SUCCESS' AND response_code= 'SUCCESS'
--     GROUP BY wallet_id)C
-- ON A.wallet_id = C.wallet_id
-- WHERE  Date('2022-01-15') > Date(IF((B.otp_date IS NOT NULL) AND (B.otp_date > C.last_txn_dt), B.otp_date, C.last_txn_dt) + 365 DAY)

-- -- 1,40,61,086
-- SELECT COUNT(DISTINCT A.wallet_id) AS walletsDeactivated FROM
--     (SELECT DISTINCT wallet_id FROM wallet.wallet
--     WHERE Date(created_at) <= '2021-03-01'
--     AND is_blocked = 0 AND wallet_state = 'ACTIVATED' AND kyc_type LIKE 'MIN_KYC%')A
-- LEFT JOIN
--     (SELECT DISTINCT wallet_id, Date(created_at) as otp_date
--     FROM wallet.wallet_hurdle_details
--     WHERE year IN (2021, 2022) AND Date(created_at) <= '2022-01-15'
--     AND action = 'REVERT_WALLET_INACTIVE_STATE' AND hurdle_type = 'OTP')B
-- ON A.wallet_id = B.wallet_id
-- LEFT JOIN
--     (SELECT wallet_id, MAX(Date(txn_time)) AS last_txn_dt FROM wallet.transaction_master
--     WHERE year IN (2021, 2022) AND Date(txn_time) <= '2022-01-15'
--     AND txn_type = 'DEBIT' 
--     AND txn_state = 'SUCCESS' AND response_code= 'SUCCESS'
--     GROUP BY wallet_id)C
-- ON A.wallet_id = C.wallet_id
-- WHERE (Date('2022-01-15') > Date(IF((B.otp_date IS NOT NULL) AND (B.otp_date > C.last_txn_dt), B.otp_date, C.last_txn_dt) + 320 DAY))
-- OR ((B.otp_date IS NULL) AND (C.last_txn_dt IS NULL))

-- 2,63,28,404  -- 46634523
SELECT COUNT(DISTINCT CASE
WHEN (B.otp_date IS NULL) AND (C.last_txn_date IS NULL) THEN A.wallet_id
WHEN (B.otp_date IS NOT NULL) AND (C.last_txn_date IS NOT NULL) THEN if((if(B.otp_date > C.last_txn_date, B.otp_date, C.last_txn_date) + 320 DAY) < Date('2022-01-15'), A.wallet_id, null)
WHEN (B.otp_date IS NOT NULL) AND (C.last_txn_date IS NULL) THEN if((B.otp_date + 320 DAY) < Date('2022-01-15'), A.wallet_id, null)
WHEN (B.otp_date IS NULL) AND (C.last_txn_date IS NOT NULL) THEN if((C.last_txn_date + 320 DAY) < Date('2022-01-15'), A.wallet_id, null)
END) FROM
    (SELECT DISTINCT wallet_id FROM wallet.wallet
    WHERE Date(created_at) <= '2021-03-01'
    AND is_blocked = 0 
    AND wallet_state <> 'DEACTIVATED' 
    AND kyc_type LIKE 'MIN_KYC%')A
LEFT JOIN
    (SELECT DISTINCT wallet_id, Date(created_at) as otp_date
    FROM wallet.wallet_hurdle_details
    WHERE year IN (2021, 2022) AND Date(created_at) <= '2022-01-15'
    AND action = 'REVERT_WALLET_INACTIVE_STATE' AND hurdle_type = 'OTP')B
ON A.wallet_id = B.wallet_id
LEFT JOIN
    (SELECT wallet_id, MAX(Date(txn_time)) AS last_txn_date FROM wallet.transaction_master
    WHERE year IN (2021, 2022) AND Date(txn_time) <= '2022-01-15'
    AND txn_type = 'DEBIT' 
    AND txn_state = 'SUCCESS' AND response_code= 'SUCCESS'
    GROUP BY wallet_id)C
ON A.wallet_id = C.wallet_id