users who did > 5k topup but not HML classified

%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