userId - wallet_id - card_masked_no mapping

PHOTO

Thu Nov 03 2022 08:48:46 GMT+0000 (Coordinated Universal Time)

SELECT A.user_id, A.wallet_id, B.transaction_id, B.transaction_time, B.topupAmt,
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_acc_id
FROM
    (SELECT DISTINCT user_ext_id as user_id, wallet_id FROM users.users
    WHERE user_ext_id 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'))A
INNER JOIN
    (SELECT senderuserid, transaction_id, transaction_time, totaltransactionamount AS topupAmt,
    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 workflowtype = 'CONSUMER_TO_MERCHANT'
    and pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
    and receiveruser IN ('PHONEPEWALLETTOPUP','NEXUSWALLETTOPUP') AND card_flag = true)B
ON A.user_id = B.senderuserid
INNER JOIN
    (SELECT eventdata_senderuser AS user_id, eventdata_transactionid AS txn_id, eventdata_sendermaskaccountid as masked_acc_id
    FROM foxtrot_stream.payment_backend_transact
    WHERE year = 2022 and month= 9 and eventdata_senderuser 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'))C
ON A.user_id = C.user_id AND B. transaction_id - C.txn_id

COPY