%jdbc(hive) set tez.queue.name=fra_operations; set hive.execution.engine=tez; -- users who did > 5k topup but didnt get HML classified select A.user_ext_id, A.wallet_id, NVL(sum_wallettopuptrn, 0) AS wallet_amt, NVL(egv_amt, 0) as egv_amt , (NVL(sum_wallettopuptrn, 0) + NVL(egv_amt, 0)) AS tot_topup from (SELECT user_ext_id, wallet_id , IF(registration_date IS NOT NULL, registration_date, created) as registration_date, updated FROM users.users WHERE IF(registration_date IS NOT NULL, registration_date, created) <= date_sub('2023-04-27', 1))A INNER JOIN (SELECT DISTINCT wallet_id FROM wallet.wallet WHERE wallet_state = 'ACTIVATED')B ON A.wallet_id = B.wallet_id LEFT JOIN (select wallet_id, sum(amount)/100 as 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)/100 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 LEFT join (select user_id FROM fraud.hml_classification_log3 WHERE run_date = '2023-04-27')E on A.user_ext_id = E.user_id where ((sum_wallettopuptrn IS NOT NULL AND egv_amt IS NOT NULL AND (sum_wallettopuptrn + egv_amt) > 5000) or ((egv_amt > 5000) and isnull(sum_wallettopuptrn)) or ((sum_wallettopuptrn > 5000) and isnull(egv_amt))) and E.user_id is null