Txn level data : Landlord POV

%jdbc(hive)
set tez.queue.name=default;
set hive.execution.engine=tez;
 
with base as
(SELECT DISTINCT A.landlord
, IF(B.phone_number IS NOT NULL, B.phone_number, NULL) AS landlord_phoneno
, IF(C.reciver_vpa IS NOT NULL, C.reciver_vpa, NULL) AS landlord_vpa
, IF(B.user_id IS NOT NULL, B.user_id, C.user_id) as landlord_user_id
, A.sender
, A.txn_id, A.amt, A.CC
, A.month, A.day, A.timee
, A.eventdata_senderglobalcardid, A.eventdata_receiverbankifsc, A.eventdata_receiverbankaccountsha, A.eventdata_receivermaskaccountid
FROM
    (select distinct eventdata_merchantcontext_contactid as landlord
    , eventdata_transactionid AS txn_id
    ,eventdata_sender as sender
    ,eventdata_senderphone
    ,eventdata_totaltransactionamount/100 as amt
    ,eventdata_senderglobalcardid as CC
    ,month, day, `time` as timee, eventdata_senderglobalcardid, eventdata_receiverbankifsc, eventdata_receiverbankaccountsha, eventdata_receivermaskaccountid
    from foxtrot_stream.payment_backend_transact
    where 0 = 0
    and year = 2022 and month = 10 ------------------- CHANGE THIS IF REQUIRED
    and eventtype = 'PAYMENT'
    and eventdata_status = 'COMPLETED'
    and eventdata_receiveruser = 'PHONEPERENTPAYMENT'
    and eventdata_sendertype = 'INTERNAL_USER'
    AND eventdata_workflowtype = 'CONSUMER_TO_MERCHANT'
    AND eventdata_receiversubtype = 'ONLINE_MERCHANT'
    AND eventdata_receivertypeinrequest = 'MERCHANT'
    AND eventdata_receivertype = 'MERCHANT'
    AND ((LENGTH(eventdata_merchantcontext_contactid) = 10) OR (eventdata_merchantcontext_contactid LIKE '%@ybl') OR (eventdata_merchantcontext_contactid LIKE '%@ibl') OR (eventdata_merchantcontext_contactid LIKE '%@axl')))A
LEFT JOIN
    (SELECT DISTINCT user_ext_id AS user_id, phone_number
    FROM users.users
    WHERE blacklisted = 0)B
ON A.landlord = B.phone_number
LEFT JOIN
    (SELECT DISTINCT receiveruser as user_id, reciver_vpa
    FROM fraud.transaction_details_v3
    WHERE year(updated_date) = 2022 AND Month(updated_date) = 10 ------------------- CHANGE THIS IF REQUIRED
    AND errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
    AND ((reciver_vpa LIKE '%@ybl') OR (reciver_vpa LIKE '%@ibl') OR (reciver_vpa LIKE '%@axl')))C
ON A.landlord = C.reciver_vpa)
 
SELECT DISTINCT A.sender, A.landlord_user_id, A.landlord, A.txn_id, A.amt, A.month, A.day, A.timee, A.eventdata_senderglobalcardid
, A.eventdata_receiverbankifsc, A.eventdata_receiverbankaccountsha, A.eventdata_receivermaskaccountid FROM
    (SELECT sender, landlord_user_id, landlord, txn_id, amt, month, day, timee, eventdata_senderglobalcardid, eventdata_receiverbankifsc, eventdata_receiverbankaccountsha, eventdata_receivermaskaccountid
    FROM base
    WHERE landlord_user_id IS NOT NULL)A
INNER JOIN
    (SELECT DISTINCT sender FROM base
    where landlord_user_id IN ------------------- CHANGE THIS IF REQUIRED
    ('U1712101226474860706675','U2004061302413273169962','U1910201422273739177907','U1611120053088447066378','U1611141714021379188138','U1611111841083258830739'))B
ON A.sender = B.sender