Monami : wallet queries combined

%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