user_id - txn-id - masked_card_no mapping

PHOTO

Fri Nov 04 2022 09:16:06 GMT+0000 (Coordinated Universal Time)

SELECT B.user_id, B.transaction_id, B.transaction_time, B.txnAmt,
B.card_type, B.card_holder_name, B.card_issuer, B.is_saved_card, B.senderglobalcardid, B.sendercard_type, B.sendercardbin, B.sendercardbankid
, C.masked_card_number
FROM
    (SELECT senderuserid as user_id, transaction_id, transaction_time
     , totaltransactionamount AS txnAmt, card_type, card_holder_name, card_issuer
     , is_saved_card, senderglobalcardid
     , sendercard_type, sendercardbin, sendercardbankid
    from fraud.transaction_details_v3
    where year(updated_date) = 2022 and month(updated_date) = 9
    AND senderuserid IN ('U2110241916318205871081', 'U2101221250122689468012', 'U2112282013439344159321', 'U2110241642481807694848', 'U2107191951238020256439', 'U1902152038347010723952', 'U2205271514223828768249', 'U2109021517074404803427', 'U2112150747596923798283', 'U2207271819162469576415', 'U2107292052315775544822', 'U1710152015505684404281', 'U2110021431485385239216', 'U2010292114205320971683', 'U2202161915384229071660', 'U1903222026344297838871', 'U2007102000200372037263', 'U1909241158097217416551', 'U2208072142583110871728', 'U2107141135063764533304', 'U2011291301178698823590', 'U2110131914051889281007', 'U1611110659131094577687', 'U2008012126366588542105', 'U2208171046204852539784', 'U2002181754215248302889', 'U1705210144206369157895', 'U1811201226479167416690', 'U2005021929293731241365', 'U2012031922268551181650', 'U2205310620179387086767', 'U2004200845330413537123', 'U2112150836343983381594', 'U2002191351514629136543')
    and sendertype = 'INTERNAL_USER'
    and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
    AND card_flag = true)B
INNER JOIN
    (SELECT transaction_id as txn_id, masked_card_number
    FROM  payment.transaction_payer_sources
    WHERE year = 2022 and month= 9 AND state = 'COMPLETED')C
ON B.transaction_id = C.txn_id

COPY