hml classification test using masked card no instead of senderglobalcardid

%jdbc(hive)
set tez.queue.name=torch;
set hive.execution.engine=tez;

SELECT hml_category, COUNT(senderuserid) AS users FROM
(select DISTINCT senderuserid,
    (case when collectset_upi_banks > 8 or
    collectset_cards > 10 or
    merchants > 90 or
    sum_success_walletwithdrawaltrn > 30000 or
    cnt_bf_trn > 30 or
    rcv_imps_banks > 1 or
    sum_hr > 50000 or
    sum_success_hr > 30000 then 'HIGH RISK'
when
    (collectset_upi_banks > 4 or
    collectset_cards > 4 or
    merchants > 25 or
    sum_success_walletwithdrawaltrn > 0 or
    cnt_bf_trn > 15 or
    rcv_imps_banks > 0 or
    sum_hr > 0 or
    sum_success_hr > 0)
    and age < 120 then 'HIGH RISK'
when
    (collectset_upi_banks > 4 or
    collectset_cards > 4 or
    merchants > 25 or
    sum_success_walletwithdrawaltrn > 0 or
    cnt_bf_trn > 15 or
    rcv_imps_banks > 0 or
    sum_hr > 0 or
    sum_success_hr > 0)
    and age >= 120 then 'MEDIUM RISK'
else NULL end) as hml_category FROM 
(select B.user_ext_id AS senderuserid, age 

--- Transactions
,count(distinct transaction_id) as cnt_trn
,count(distinct case when errorcode = 'SUCCESS' then transaction_id end) as cnt_success_trn
,count(distinct case when errorcode != 'SUCCESS' then transaction_id end) as cnt_bf_trn
--- transaction sum
,sum(totaltransactionamount) as sum_trn
,sum(case when errorcode = 'SUCCESS' then totaltransactionamount end) as sum_success_trn
,sum(case when errorcode != 'SUCCESS' then totaltransactionamount end) as sum_bf_trn
,cnt_wallettopuptrn
,sum_wallettopuptrn
,count(distinct (case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') then transaction_id end)) as cnt_walletwithdrawaltrn
,count(distinct (case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode = 'SUCCESS' then transaction_id end)) as cnt_success_walletwithdrawaltrn
,count(distinct (case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode != 'SUCCESS' then transaction_id end)) as cnt_bf_walletwithdrawaltrn
--- workflow split sum (wallet wtihdrawal)
,sum(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') then totaltransactionamount end) as sum_walletwithdrawaltrn
,sum(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode = 'SUCCESS' then totaltransactionamount end) as sum_success_walletwithdrawaltrn
,sum(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode != 'SUCCESS' then totaltransactionamount end) as sum_bf_walletwithdrawaltrn
-- distinct card count
,count(distinct (case when card_flag = 1 then masked_card_number end)) as collectset_cards
,count(distinct (case when card_flag = 1 and sendercard_type = 'DEBIT_CARD' then masked_card_number end)) as  pg_debitcard
,count(distinct (case when card_flag = 1 and sendercard_type = 'CREDIT_CARD' then masked_card_number end)) as collectset_pg_credit_card
-- count of success cards
,count(distinct (case when card_flag = 1 and errorcode = 'SUCCESS' then masked_card_number end)) as collectset_success_cards
,count(distinct (case when card_flag = 1 and sendercard_type = 'DEBIT_CARD' and errorcode = 'SUCCESS' then masked_card_number end)) as collectset_success_pg_debitcard
,count(distinct (case when card_flag = 1 and sendercard_type = 'CREDIT_CARD' and errorcode = 'SUCCESS' then masked_card_number end)) as collectset_success_pg_credit_card
--- cards bf
,count(distinct (case when card_flag = 1 and errorcode != 'SUCCESS' then masked_card_number end)) as collectset_bf_cards
,count(distinct (case when card_flag = 1 and sendercard_type = 'DEBIT_CARD' and errorcode != 'SUCCESS' then masked_card_number end)) as collectset_bf_pg_debitcard
,count(distinct (case when card_flag = 1 and sendercard_type = 'CREDIT_CARD' and errorcode != 'SUCCESS' then masked_card_number end)) as collectset_bf_pg_credit_card
--banks count
,count(distinct(case when upi_flag = true then senderbankaccountsha end)) as collectset_upi_banks
,count(distinct(case when upi_flag = true and errorcode = 'SUCCESS' then senderbankaccountsha end)) as collectset_success_upi_banks
,count(distinct(case when upi_flag = true and errorcode != 'SUCCESS' then senderbankaccountsha end)) as collectset_bf_upi_banks
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') then senderbankaccountsha end)) as collectset_imps_banks
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode = 'SUCCESS' then senderbankaccountsha end)) as collectset_success_imps_banks
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode != 'SUCCESS' then senderbankaccountsha end)) as collectset_bf_imps_banks
-- devices
,count(distinct devicefingerprint) as collectset_devices
-- workflow split cnt (CONSUMER_TO_MERCHANT lifetime)
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') then receiveruser end)) as merchants
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode = 'SUCCESS' then receiveruser end)) as merchants_suc
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode != 'SUCCESS' then receiveruser end)) as merchants_bf
-- workflow split cnt (CONSUMER_TO_MERCHANT lifetime)
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') then transaction_id end)) as cnt_c2mtrn
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode = 'SUCCESS' then transaction_id end)) as cnt_success_c2mtrn
,count(distinct (case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode != 'SUCCESS' then transaction_id end)) as cnt_bf_c2mtrn
--- workflow split sum (CONSUMER_TO_MERCHANT)
,sum(case when workflowtype in ('CONSUMER_TO_MERCHANT') then totaltransactionamount end) as sum_c2m_trn
,sum(case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode = 'SUCCESS' then totaltransactionamount end) as sum_success_c2m_trn
,sum(case when workflowtype in ('CONSUMER_TO_MERCHANT') and errorcode != 'SUCCESS' then totaltransactionamount end) as sum_bf_c2m_trn
-- high risk category txn
,count(distinct (case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') then transaction_id end)) as cnt_hr
,count(distinct (case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') and errorcode = 'SUCCESS' then transaction_id end)) as cnt_success_hr
,count(distinct (case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') and errorcode != 'SUCCESS' then transaction_id end)) as cnt_bf_hr
-- high risk category amnt
,sum(case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') then totaltransactionamount end) as sum_hr
,sum(case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') and errorcode = 'SUCCESS' then totaltransactionamount end) as sum_success_hr
,sum(case when receiveruser in ('MMTCPAMP', 'PHONEPEGC', 'QWIKCILVERGC', 'SAFEGOLD') and errorcode != 'SUCCESS' then totaltransactionamount end) as sum_bf_hr
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') then receiverbankaccountsha end)) as rcv_imps_banks
-- imps bank
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode = 'SUCCESS' then receiverbankaccountsha end)) as rcv_success_imps_banks
,count(distinct(case when workflowtype in ('IMPS_WALLET_WITHDRAWAL','WALLET_WITHDRAWAL') and errorcode != 'SUCCESS' then receiverbankaccountsha end)) as rcv_bf_imps_banks
FROM
    (SELECT * from fraud.above_5k_topupusers)B
left join 
    (select transaction_id
    , errorcode
    , workflowtype
    , card_flag
    , sendercard_type
    , masked_card_number
    , upi_flag
    , senderbankaccountsha
    , devicefingerprint
    , senderuserid
    , receiveruser
    , receiverbankaccountsha
    , totaltransactionamount
    from fraud.transaction_details_v3 
    where updated_date BETWEEN date_sub('2023-07-26', 90) AND date_sub('2023-07-26', 1)
    AND sendertype = 'INTERNAL_USER') A 
On B.user_ext_id = A.senderuserid    
group by B.user_ext_id, age, cnt_wallettopuptrn, sum_wallettopuptrn) A		
)X
GROUP BY hml_category