master table for last 180 days

DROP TABLE if exists fraud.fra_consumer_category_master;

CREATE  TABLE IF NOT EXISTS fraud.fra_consumer_category_master
( senderuserid	string,
updated_date	date,
c2c_txn_succ	bigint,
c2c_txn_err	bigint,
c2c_txn_total	bigint,
c2c_amt_succ	double,
c2c_amt_err	double,
c2c_amt_total	double,
c2e_user_txn_succ	bigint,
c2e_user_txn_err	bigint,
c2e_user_txn_total	bigint,
c2e_user_amt_succ	double,
c2e_user_amt_err	double,
c2e_user_amt_total	double,
c2e_mer_txn_succ	bigint,
c2e_mer_txn_err	bigint,
c2e_mer_txn_total	bigint,
c2e_mer_amt_succ	double,
c2e_mer_amt_err	double,
c2e_mer_amt_total	double,
c2a_txn_succ	bigint,
c2a_txn_err	bigint,
c2a_txn_total	bigint,
c2a_amt_succ	double,
c2a_amt_err	double,
c2a_amt_total	double,
gold_txn_succ	bigint,
gold_txn_err	bigint,
gold_txn_total	bigint,
gold_amt_succ	double,
gold_amt_err	double,
gold_amt_total	double,
edu_txn_succ	bigint,
edu_txn_err	bigint,
edu_txn_total	bigint,
edu_amt_succ	double,
edu_amt_err	double,
edu_amt_total	double,
ppgc_txn_succ	bigint,
ppgc_txn_err	bigint,
ppgc_txn_total	bigint,
ppgc_amt_succ	double,
ppgc_amt_err	double,
ppgc_amt_total	double,
gprc_txn_succ	bigint,
gprc_txn_err	bigint,
gprc_txn_total	bigint,
gprc_amt_succ	double,
gprc_amt_err	double,
gprc_amt_total	double,
recharge_txn_succ	bigint,
recharge_txn_err	bigint,
recharge_txn_total	bigint,
recharge_amt_succ	double,
recharge_amt_err	double,
recharge_amt_total	double,
billpay_txn_succ	bigint,
billpay_txn_err	bigint,
billpay_txn_total	bigint,
billpay_amt_succ	double,
billpay_amt_err	double,
billpay_amt_total	double,
voucher_txn_succ	bigint,
voucher_txn_err	bigint,
voucher_txn_total	bigint,
voucher_amt_succ	double,
voucher_amt_err	double,
voucher_amt_total	double,
wallet_topup_txn_succ	bigint,
wallet_topup_txn_err	bigint,
wallet_topup_txn_total	bigint,
wallet_topup_amt_succ	double,
wallet_topup_amt_err	double,
wallet_topup_amt_total	double,
ppcardpayment_txn_succ	bigint,
ppcardpayment_txn_err	bigint,
ppcardpayment_txn_total	bigint,
ppcardpayment_amt_succ	double,
ppcardpayment_amt_err	double,
ppcardpayment_amt_total	double,
rent_txn_succ	bigint,
rent_txn_err	bigint,
rent_txn_total	bigint,
rent_amt_succ	double,
rent_amt_err	double,
rent_amt_total	double )

CLUSTERED BY (senderuserid, updated_date) 
INTO 64 BUCKETS

STORED AS ORC
TBLPROPERTIES  
( 'bucketing_version'='2', 
  'orc.bloom.filter.columns'='', 
  'orc.bloom.filter.fpp'='0.05', 
  'orc.compress'='ZLIB', 
  'orc.create.index'='true', 
  'orc.row.index.stride'='20000', 
  'orc.stripe.size'='134217728', 
  'transactional'='true', 
  'transactional_properties'='default' 
);

INSERT OVERWRITE TABLE fraud.fra_consumer_category_master
-- MASTER TABLE FOR 180 DAYS
select senderuserid, updated_date,
--------------------C2C AGGREGATES-------------
SUM(CASE WHEN category IN ('C2C') then txn_succ else 0 end ) as c2c_txn_succ,

SUM(CASE WHEN category IN ('C2C') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as c2c_txn_err,

SUM(CASE WHEN category IN ('C2C') then txn_total else 0  end ) as c2c_txn_total,

SUM(CASE WHEN category IN ('C2C') then amt_succ else 0  end ) as c2c_amt_succ,

SUM(CASE WHEN category IN ('C2C') then amt_err else 0 end ) as c2c_amt_err,

SUM(CASE WHEN category IN ('C2C') then amt_total else 0 end ) as c2c_amt_total,

--------------------C2E USER AGGREGATES-----------
SUM(CASE WHEN category IN ('C2E_USER') then txn_succ else 0 end ) as c2e_user_txn_succ,

SUM(CASE WHEN category IN ('C2E_USER') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as c2e_user_txn_err,

SUM(CASE WHEN category IN ('C2E_USER') then txn_total else 0  end ) as c2e_user_txn_total,

SUM(CASE WHEN category IN ('C2E_USER') then amt_succ else 0  end ) as c2e_user_amt_succ,

SUM(CASE WHEN category IN ('C2E_USER') then amt_err else 0 end ) as c2e_user_amt_err,

SUM(CASE WHEN category IN ('C2E_USER') then amt_total else 0 end ) as c2e_user_amt_total,

--------------------C2E MERCHANT AGGREGATES-----------
SUM(CASE WHEN category IN ('C2E_MER') then txn_succ else 0 end ) as c2e_mer_txn_succ,

SUM(CASE WHEN category IN ('C2E_MER') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as c2e_mer_txn_err,

SUM(CASE WHEN category IN ('C2E_MER') then txn_total else 0  end ) as c2e_mer_txn_total,

SUM(CASE WHEN category IN ('C2E_MER') then amt_succ else 0  end ) as c2e_mer_amt_succ,

SUM(CASE WHEN category IN ('C2E_MER') then amt_err else 0 end ) as c2e_mer_amt_err,

SUM(CASE WHEN category IN ('C2E_MER') then amt_total else 0 end ) as c2e_mer_amt_total,

--------------------C2A AGGREGATES--------------------
SUM(CASE WHEN category IN ('C2A') then txn_succ else 0 end ) as c2a_txn_succ,

SUM(CASE WHEN category IN ('C2A') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as c2a_txn_err,

SUM(CASE WHEN category IN ('C2A') then txn_total else 0  end ) as c2a_txn_total,

SUM(CASE WHEN category IN ('C2A') then amt_succ else 0  end ) as c2a_amt_succ,

SUM(CASE WHEN category IN ('C2A') then amt_err else 0 end ) as c2a_amt_err,

SUM(CASE WHEN category IN ('C2A') then amt_total else 0 end ) as c2a_amt_total,

--------------------GOLD AGGREGATES-----------
SUM(CASE WHEN category IN ('GOLD') then txn_succ else 0 end ) as gold_txn_succ,

SUM(CASE WHEN category IN ('GOLD') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as gold_txn_err,

SUM(CASE WHEN category IN ('GOLD') then txn_total else 0  end ) as gold_txn_total,

SUM(CASE WHEN category IN ('GOLD') then amt_succ else 0  end ) as gold_amt_succ,

SUM(CASE WHEN category IN ('GOLD') then amt_err else 0 end ) as gold_amt_err,

SUM(CASE WHEN category IN ('GOLD') then amt_total else 0 end ) as gold_amt_total,

--------------------EDUCATION AGGREGATES-----------
SUM(CASE WHEN category IN ('EDUCATION') then txn_succ else 0 end ) as edu_txn_succ,

SUM(CASE WHEN category IN ('EDUCATION') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as edu_txn_err,

SUM(CASE WHEN category IN ('EDUCATION') then txn_total else 0  end ) as edu_txn_total,

SUM(CASE WHEN category IN ('EDUCATION') then amt_succ else 0  end ) as edu_amt_succ,

SUM(CASE WHEN category IN ('EDUCATION') then amt_err else 0 end ) as edu_amt_err,

SUM(CASE WHEN category IN ('EDUCATION') then amt_total else 0 end ) as edu_amt_total,

--------------------PHONEPEGC AGGREGATES-----------
SUM(CASE WHEN category IN ('PHONEPE_GC') then txn_succ else 0 end ) as ppgc_txn_succ,

SUM(CASE WHEN category IN ('PHONEPE_GC') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as ppgc_txn_err,

SUM(CASE WHEN category IN ('PHONEPE_GC') then txn_total else 0  end ) as ppgc_txn_total,

SUM(CASE WHEN category IN ('PHONEPE_GC') then amt_succ else 0  end ) as ppgc_amt_succ,

SUM(CASE WHEN category IN ('PHONEPE_GC') then amt_err else 0 end ) as ppgc_amt_err,

SUM(CASE WHEN category IN ('PHONEPE_GC') then amt_total else 0 end ) as ppgc_amt_total,

--------------------GPRC AGGREGATES-----------
SUM(CASE WHEN category IN ('GPRC') then txn_succ else 0 end ) as gprc_txn_succ,

SUM(CASE WHEN category IN ('GPRC') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as gprc_txn_err,

SUM(CASE WHEN category IN ('GPRC') then txn_total else 0  end ) as gprc_txn_total,

SUM(CASE WHEN category IN ('GPRC') then amt_succ else 0  end ) as gprc_amt_succ,

SUM(CASE WHEN category IN ('GPRC') then amt_err else 0 end ) as gprc_amt_err,

SUM(CASE WHEN category IN ('GPRC') then amt_total else 0 end ) as gprc_amt_total,

--------------------RECHARGES AGGREGATES-----------
SUM(CASE WHEN category IN ('RECHARGES') then txn_succ else 0 end ) as recharge_txn_succ,

SUM(CASE WHEN category IN ('RECHARGES') then txn_err else 0  end ) as recharge_txn_err,

SUM(CASE WHEN category IN ('RECHARGES') then txn_total else 0  end ) as recharge_txn_total,

SUM(CASE WHEN category IN ('RECHARGES') then amt_succ else 0  end ) as recharge_amt_succ,

SUM(CASE WHEN category IN ('RECHARGES') then amt_err else 0 end ) as recharge_amt_err,

SUM(CASE WHEN category IN ('RECHARGES') then amt_total else 0 end ) as recharge_amt_total,

-------------------- BILLPAY AGGREGATES-----------
SUM(CASE WHEN category IN ('BILLPAY') then txn_succ else 0 end ) as billpay_txn_succ,

SUM(CASE WHEN category IN ('BILLPAY') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as billpay_txn_err,

SUM(CASE WHEN category IN ('BILLPAY') then txn_total else 0  end ) as billpay_txn_total,

SUM(CASE WHEN category IN ('BILLPAY') then amt_succ else 0  end ) as billpay_amt_succ,

SUM(CASE WHEN category IN ('BILLPAY') then amt_err else 0 end ) as billpay_amt_err,

SUM(CASE WHEN category IN ('BILLPAY') then amt_total else 0 end ) as billpay_amt_total,

-------------------- VOUCHER AGGREGATES-----------
SUM(CASE WHEN category IN ('VOUCHERS') then txn_succ else 0 end ) as voucher_txn_succ,

SUM(CASE WHEN category IN ('VOUCHERS') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as voucher_txn_err,

SUM(CASE WHEN category IN ('VOUCHERS') then txn_total else 0  end ) as voucher_txn_total,

SUM(CASE WHEN category IN ('VOUCHERS') then amt_succ else 0  end ) as voucher_amt_succ,

SUM(CASE WHEN category IN ('VOUCHERS') then amt_err else 0 end ) as voucher_amt_err,

SUM(CASE WHEN category IN ('VOUCHERS') then amt_total else 0 end ) as voucher_amt_total,

--------------------WALLET_TOPUP AGGREGATES-----------
SUM(CASE WHEN category IN ('WALLET_TOPUPS') then txn_succ else 0 end ) as wallet_topup_txn_succ,

SUM(CASE WHEN category IN ('WALLET_TOPUPS') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as wallet_topup_txn_err,

SUM(CASE WHEN category IN ('WALLET_TOPUPS') then txn_total else 0  end ) as wallet_topup_txn_total,

SUM(CASE WHEN category IN ('WALLET_TOPUPS') then amt_succ else 0  end ) as wallet_topup_amt_succ,

SUM(CASE WHEN category IN ('WALLET_TOPUPS') then amt_err else 0 end ) as wallet_topup_amt_err,

SUM(CASE WHEN category IN ('WALLET_TOPUPS') then amt_total else 0 end ) as wallet_topup_amt_total,

-----------------CARD PAYYMENT AGGREGATES-------------
SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') then txn_succ else 0 end ) as ppcardpayment_txn_succ,

SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as ppcardpayment_txn_err,

SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') then txn_total else 0  end ) as ppcardpayment_txn_total,

SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') then amt_succ else 0  end ) as ppcardpayment_amt_succ,

SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') then amt_err else 0 end ) as ppcardpayment_amt_err,

SUM(CASE WHEN category IN ('PHONEPECARDPAYMENT') then amt_total else 0 end ) as ppcardpayment_amt_total,

-------------------------------- RENT PAYMENT AGGREGATES----------------------------
SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') then txn_succ else 0 end ) as rent_txn_succ,

SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') and  updated_date >= date_sub(current_date,7) then txn_err else 0  end ) as rent_txn_err,

SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') then txn_total else 0  end ) as rent_txn_total,

SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') then amt_succ else 0  end ) as rent_amt_succ,

SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') then amt_err else 0 end ) as rent_amt_err,

SUM(CASE WHEN category IN ('PHONEPERENTPAYMENT') then amt_total else 0 end ) as rent_amt_total
from
(select senderuserid, updated_date,
case when workflowtype IN ('CONSUMER_TO_CONSUMER', 'CONSUMER_TO_CONSUMER_V2') THEN 'C2C'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')  AND mcc in ('0000','0',null) THEN 'C2E_USER'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')   AND mcc NOT IN ('0000','0',null) THEN 'C2E_MER'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')   AND lower(reciver_vpa) like '%ifsc.npci'  THEN 'C2A'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT' AND  receiveruser in ('SAFEGOLD','MMTCPAMP') THEN 'GOLD'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT' AND  receiveruser in ('PHONEPEEDUPAYMENT') THEN 'EDUCATION'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
 AND  receiveruser in ('PHONEPEGC') THEN 'PHONEPE_GC'
 
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND  receiveruser in ('EURONET') and nex_category = 'GP'and accountingproviderid = 'GOOGLERCNP' THEN 'GPRC'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('RECHARGE' ) and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') THEN 'RECHARGES'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('BILLPAY' ) and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') and nex_category <> 'CC'  THEN 'BILLPAY' 

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('VOUCHER' )  and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') THEN 'VOUCHERS'

when  (workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND receiveruser in ('NEXUSWALLETTOPUP','PHONEPEWALLETTOPUP') ) OR (workflowtype IN ('WALLET_TOPUP' , 'WALLET_TOPUP_V2') AND  receivertype = 'INTERNAL_USER')  THEN 'WALLET_TOPUPS'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiveruser IN ('PHONEPECARDPAYMENT','CYBSYESB' , 'BILLDESKHEXALL' , 'MCYESB')  and nex_category = 'CC' THEN 'PHONEPECARDPAYMENT'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiveruser IN ('PHONEPERENTPAYMENT') THEN 'PHONEPERENTPAYMENT'
 
ELSE 'OTHERS' end as category,
count(distinct case when pay_transaction_status = 'COMPLETED' then transaction_id end ) as txn_succ,
count(distinct case when errorcode not in ('SUCCESS','BLOCKED_FRAUD') then transaction_id end ) as txn_err,
count(distinct transaction_id ) as txn_total,
SUM(case when pay_transaction_status = 'COMPLETED' then totaltransactionamount end) as amt_succ,
SUM(case when errorcode not in ('SUCCESS','BLOCKED_FRAUD') then totaltransactionamount end) as amt_err,
SUM(totaltransactionamount) as amt_total
FROM
    (SELECT DISTINCT senderuserid, updated_date, workflowtype, receivertype, pay_transaction_status, errorcode, transaction_id, totaltransactionamount
    , receiveruser, mcc, reciver_vpa, receiversubtype, nex_category, accountingproviderid, nex_transaction_type
    FROM fraud.transaction_details_v3 
    where updated_date >= date_sub(current_date, 182) 
     and sendertype = 'INTERNAL_USER' AND senderuserid is not null AND senderuserid <> ''
    and workflowtype IN ( 'CONSUMER_TO_MERCHANT', 'CONSUMER_TO_MERCHANT_V2', 'CONSUMER_TO_CONSUMER', 'CONSUMER_TO_CONSUMER_V2', 'CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2'))td 

group by senderuserid, 
updated_date,
-- td.workflowtype, td.mcc, td.reciver_vpa, td.receivertype, td.receiveruser, td.receiversubtype, td.nex_category, td.accountingproviderid
case when workflowtype IN ('CONSUMER_TO_CONSUMER', 'CONSUMER_TO_CONSUMER_V2') THEN 'C2C'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')  AND mcc in ('0000','0',null) THEN 'C2E_USER'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')   AND mcc NOT IN ('0000','0',null) THEN 'C2E_MER'
when workflowtype IN ('CONSUMER_TO_EXTERNAL', 'CONSUMER_TO_EXTERNAL_V2')   AND lower(reciver_vpa) like '%ifsc.npci'  THEN 'C2A'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT' AND  receiveruser in ('SAFEGOLD','MMTCPAMP') THEN 'GOLD'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT' AND  receiveruser in ('PHONEPEEDUPAYMENT') THEN 'EDUCATION'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND  receiveruser in ('PHONEPEGC') THEN 'PHONEPE_GC'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND  receiveruser in ('EURONET') and nex_category = 'GP'and accountingproviderid = 'GOOGLERCNP' THEN 'GPRC'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('RECHARGE' ) and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') THEN 'RECHARGES'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('BILLPAY' ) and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') and nex_category <> 'CC'  THEN 'BILLPAY' 

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND nex_transaction_type IN ('VOUCHER' )  and receiveruser NOT IN ('PHONEPECARDPAYMENT' , 'CYBSYESB' , 'PHONEPERENTPAYMENT', 'BILLDESKHEXALL' , 'MCYESB') THEN 'VOUCHERS'

when  (workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiversubtype = 'ONLINE_MERCHANT'
AND receiveruser in ('NEXUSWALLETTOPUP','PHONEPEWALLETTOPUP') ) OR (workflowtype IN ('WALLET_TOPUP' , 'WALLET_TOPUP_V2') AND  receivertype = 'INTERNAL_USER')  THEN 'WALLET_TOPUPS'

when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiveruser IN ('PHONEPECARDPAYMENT','CYBSYESB' , 'BILLDESKHEXALL' , 'MCYESB')  and nex_category = 'CC' THEN 'PHONEPECARDPAYMENT'
when workflowtype IN ('CONSUMER_TO_MERCHANT' , 'CONSUMER_TO_MERCHANT_V2') AND receivertype = 'MERCHANT'  AND receiveruser IN ('PHONEPERENTPAYMENT') THEN 'PHONEPERENTPAYMENT'
ELSE 'OTHERS' end)X
GROUP BY senderuserid, updated_date
;