-- 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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter