single_cc_mult_wallet_topup

-- same card used for mult wallet topup
SELECT CONCAT(masked_card_number, " - " , card_issuer,  " - " , bank_id, " - " , sendercard_type) AS Identifier
, COUNT(DISTINCT A.senderuserid) as wallets_toppedup
, COUNT(DISTINCT A.updated_date) AS active_days
, SUM(A.totaltransactionamount) AS value
, COUNT(DISTINCT A.transaction_id) as txns
FROM
    (SELECT senderuserid, totaltransactionamount, transaction_id, card_issuer, sendercard_type, updated_date 
    from fraud.transaction_details_v3
    where updated_date BETWEEN DATE_SUB('2023-01-13', 7) AND DATE_SUB('2023-01-13', 1)
    and sendertype = 'INTERNAL_USER' AND workflowtype = 'CONSUMER_TO_MERCHANT'
    and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
    and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') AND (upi_flag = true OR card_flag = true))A
INNER JOIN
    (SELECT transaction_id, masked_card_number, bank_id
    FROM payment.transaction_payer_sources
    WHERE year IN (year(DATE_SUB('2023-01-13', 7)), year(DATE_SUB('2023-01-13', 1)))
    AND month IN (month(DATE_SUB('2023-01-13', 7)), month(DATE_SUB('2023-01-13', 1)))
    AND masked_card_number IS NOT NULL)B
ON A.transaction_id = B.transaction_id
GROUP BY CONCAT(masked_card_number, " - " , card_issuer,  " - " , bank_id, " - " , sendercard_type)
ORDER BY wallets_toppedup DESC, value DESC
LIMIT 20