-- 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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter