SENDER POV CASE 2 TXN LEVEL DATA

-- SENDER POV CASE 2 TXN LEVEL DATA Aug
with base as
(SELECT DISTINCT A.sender, A.senderphone
, A.landlord
-- , 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.txn_id, A.amt, A.CC FROM
(select distinct eventdata_merchantcontext_contactid as landlord
, eventdata_transactionid as txn_id
,eventdata_sender as sender
,eventdata_senderphone as senderphone
,eventdata_totaltransactionamount/100 as amt
,eventdata_senderglobalcardid as CC
from foxtrot_stream.payment_backend_transact
where 0 = 0
and year = 2022 and month = 8
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) = 8 
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 C.* FROM
(select sender, count(distinct landlord_user_id) as landlords
, SUM(amt) as tot_rent_paid, count(distinct txn_id) as txn_cnt
, COUNT(DISTINCT CC) AS CCused
from base
group by sender
HAVING landlords > 1 AND tot_rent_paid < 100000 AND CCused > 1)A
INNER JOIN
(SELECT DISTINCT landlord, sender-- ALL landlords of senders in table A
FROM base)B
on A.sender = B.sender
INNER JOIN
    (SELECT * FROM base)C
ON B.landlord = C.landlord