RFI 8

SELECT senderuserid
, COUNT(DISTINCT senderbankaccountsha) as bank_accs
, SUM(CASE WHEN sendercard_type = 'CREDIT_CARD' THEN (totaltransactionamount * 0.977) ELSE totaltransactionamount END) AS topup_amt
, COUNT(DISTINCT transaction_id) as txn_cnt
from fraud.transaction_details_v3
where updated_date BETWEEN DATE_SUB('2023-05-26', 31) AND DATE_SUB('2023-05-26', 1)
and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT'
and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') 
GROUP BY senderuserid
ORDER BY bank_accs DESC, topup_amt DESC
LIMIT 100

---------------------------------------------------------------------------------------
  SELECT senderuserid
-- , COUNT(DISTINCT senderbankaccountsha) as tot_bank_accs
, COUNT(DISTINCT CASE WHEN upi_flag = TRUE THEN senderbankaccountsha ELSE NULL END) AS upi_bank_acc
, COUNT(DISTINCT CASE WHEN card_flag = TRUE AND sendercard_type = 'CREDIT_CARD' AND is_saved_card = TRUE THEN unique_card_id ELSE NULL END) AS cc_cnt
, COUNT(DISTINCT CASE WHEN card_flag = TRUE AND sendercard_type = 'DEBIT_CARD' AND is_saved_card = TRUE THEN unique_card_id ELSE NULL END) AS dc_bank_acc

-- , SUM(CASE WHEN sendercard_type = 'CREDIT_CARD' THEN (totaltransactionamount * 0.977) ELSE totaltransactionamount END) AS tot_topup_amt
, SUM(CASE WHEN upi_flag = TRUE THEN totaltransactionamount ELSE 0 END) AS upi_amt
, SUM(CASE WHEN card_flag = TRUE AND sendercard_type = 'CREDIT_CARD' THEN (totaltransactionamount * 0.977) ELSE 0 END) AS cc_amt
, SUM(CASE WHEN card_flag = TRUE AND sendercard_type = 'DEBIT_CARD' THEN totaltransactionamount ELSE 0 END) AS dc_amt

, COUNT(DISTINCT transaction_id) as tot_txn_cnt
, COUNT(DISTINCT CASE WHEN upi_flag = TRUE THEN transaction_id ELSE NULL END) AS upi_txn_cnt
, COUNT(DISTINCT CASE WHEN card_flag = TRUE AND sendercard_type = 'CREDIT_CARD' AND is_saved_card = TRUE THEN transaction_id ELSE NULL END) AS cc_txn_cnt
, COUNT(DISTINCT CASE WHEN card_flag = TRUE AND sendercard_type = 'DEBIT_CARD' AND is_saved_card = TRUE THEN transaction_id ELSE NULL END) AS dc_txn_cnt

from fraud.transaction_details_v3
where updated_date BETWEEN DATE_SUB('2023-05-02', 31) AND DATE_SUB('2023-05-02', 1)
and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT'
and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') 
GROUP BY senderuserid
ORDER BY (upi_bank_acc + dc_bank_acc) DESC, (upi_amt + dc_amt) DESC
LIMIT 50