wallets to be deactivated
Wed Sep 28 2022 11:15:39 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%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
Comments