categorized users who did <5k topup

%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));