Monami : wallet queries combined
Mon Nov 07 2022 09:24:52 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive) set tez.queue.name=default; set hive.execution.engine=tez; select a.*, b.utr, b.sender_account_number, d.receiver_account_number, c.district, e.masked_acc_id, f.eventdata_globalcardid as globalcardid, f.CardTokenEventDate, P.wallet_id, Q.wallet_id, Q.merchant_reference_id, Q.merchant_id, Q.category, Q.sub_category, Q.amount, Q.currency_code, Q.txn_type, Q.txn_state, Q.txn_time, Q.created_at, Q.updated_at, Q.closing_available_balance FROM (select senderphonenumber, senderuserid, sendertype, receiverphonenumber, receiveruser, receivertype, receiversubtype, accountingproviderid, transaction_id, transaction_time, pay_transaction_status, workflowtype, transfermode, totaltransactionamount, senderupiamount, senderwalletamount, senderpgamount, senderbankid, senderbankaccountsha, senderbankifsc, sendernameonbankaccount, receiverbankifsc, receiverbankid, receiverbankaccountsha, message, device_manufacturer, ip, device_advertiser_id, latitude, logitude, sender_vpa, reciver_vpa, senderglobalcardid from fraud.transaction_details_v3 where updated_date BETWEEN '2022-10-01' AND '2022-10-31' -- only list of merchants: -- AND receivertype = 'MERCHANT' AND (senderuserid in ('MNIT6A8LJ','M33JYFACO') or receiveruser in ('MNIT6A8LJ', 'M33JYFACO')) -- only phone no -- AND ((senderphonenumber = '8999167190') OR (receiverphonenumber = '8999167190')) -- only userId AND ((senderuserid in ('U2110021431485385239216')) or (receiveruser in ('U2110021431485385239216'))) -- combined list of userids & phone nos -- AND ((senderphonenumber = '8999167190') OR (receiverphonenumber = '8999167190') OR (senderuserid = 'U2110291351325847772230') or (receiveruser = 'U2110291351325847772230')) -- use IN ('', '', '') instead of "=", if there are multiple phone nos or userIds and errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED') a left join (select transaction_id, account_number as sender_account_number, UTR from payment.transaction_payer_sources where year = 2022) b on a.transaction_id = b.transaction_id left join (SELECT user_id, district FROM edw_shared.user_dimension where user_id in ('U2110021431485385239216')) c on a.senderuserid = c.user_id left join (select transaction_id, account_number as receiver_account_number from payment.transaction_receiver_instruments where year = 2022) d on a.transaction_id = d.transaction_id LEFT 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= 10 and eventdata_senderuser IN ('U2110021431485385239216'))e ON a.transaction_id = e.txn_id AND a.senderuserid = e.user_id LEFT JOIN (SELECT eventdata_userid, eventdata_globalcardid, eventdata_tokenid, IF(eventtype = 'CARD_UNSTAGED', 'CARD_ADDED_PRE_TOKENIZATION', eventtype) AS event, CONCAT(year, '-', IF(month < 10, CONCAT('0', month), month), '-', IF(day < 10, CONCAT('0',day), day)) as CardTokenEventDate FROM foxtrot_stream.vault_default WHERE year = 2022 AND month <= 10 AND eventdata_userid IN ('U2110021431485385239216') AND eventtype IN ('USER_CARD_DELETED', 'CARD_UNSTAGED', 'TOKEN_REGISTERED', 'TOKEN_DELETE') AND eventdata_globalcardid <> '')f on a.senderuserid = f.eventdata_userid AND a.senderglobalcardid = f.eventdata_globalcardid LEFT JOIN (SELECT DISTINCT user_ext_id as user_id, wallet_id FROM users.users WHERE user_ext_id IN ('U2110021431485385239216'))P ON P.user_id = a.senderuserid LEFT JOIN (SELECT txn_id, wallet_id, merchant_reference_id, merchant_id, category, sub_category, amount, currency_code, txn_type, txn_state, txn_time, created_at, updated_at, closing_available_balance FROM wallet.transaction_master WHERE year = 2022 AND month = 10)Q ON P.wallet_id = Q.wallet_id AND Q.txn_id = a.transaction_id
Comments