%jdbc(hive) set tez.queue.name=phonepe_verified; set hive.execution.engine=tez; -- to find users who got categorized even tho they did <5k topup -- select COUNT(DISTINCT A.wallet_id) as wallet_id from \ SELECT A.wallet_id, C.sum_wallettopuptrn, D.egv_amt, (C.sum_wallettopuptrn + D.egv_amt) AS tot_topup_amt, B.hml_category FROM (SELECT user_ext_id, wallet_id FROM users.users WHERE IF(registration_date IS NOT NULL, registration_date, created) <= date_sub('2023-04-27', 1))A INNER JOIN (SELECT user_id, hml_category FROM fraud.hml_classification_log3 WHERE run_date = '2023-04-27')B ON A.user_ext_id = B.user_id LEFT JOIN (select wallet_id as wallet_id, sum(amount) sum_wallettopuptrn from wallet.transaction_master where year >= year(date_sub('2023-04-27',90)) AND month >= month(date_sub('2023-04-27',90)) AND month <= month(date_sub('2023-04-27',1)) AND txn_time BETWEEN date_sub('2023-04-27',90) AND date_sub('2023-04-27', 1) and txn_type = 'CREDIT' and category = 'TOPUP' group by wallet_id)C on A.wallet_id = C.Wallet_id LEFT JOIN (SELECT purchasing_user_id, SUM(original_balance) AS egv_amt --, COUNT(DISTINCT merchant_transaction_id) as gc_txns from egv.gift_cards WHERE year >= year(date_sub('2023-04-27',90)) AND month >= month(date_sub('2023-04-27',90)) AND month <= month(date_sub('2023-04-27',1)) AND date(created_at) BETWEEN date_sub('2023-04-27',90) AND date_sub('2023-04-27',1) AND program_id = 'PHONEPEGC' AND tenant_id LIKE 'PHONEPE%' AND merchant_id = 'PHONEPEGC' AND purchasing_user_id in (select users.user_ext_id from users.users join wallet.wallet on users.wallet_id = wallet.wallet_id and wallet.wallet_state = 'ACTIVATED') GROUP BY purchasing_user_id)D on A.user_ext_id = D.purchasing_user_id where ((sum_wallettopuptrn + egv_amt) < 500000) or ((egv_amt < 500000) and isnull(sum_wallettopuptrn)) or ((sum_wallettopuptrn < 500000) and isnull(egv_amt));