landlord vs tenant locations
Thu Nov 10 2022 07:11:36 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%jdbc(hive)set tez.queue.name=default; set hive.execution.engine=tez; with base as (SELECT A.sender, B.city as sender_city , A.landlord , IF(C.phone_number IS NOT NULL, C.phone_number, NULL) AS landlord_phoneno , IF(CC.primary_vpa IS NOT NULL, CC.primary_vpa, NULL) AS landlord_vpa , IF(C.user_id IS NOT NULL, C.user_id, CC.user_id) as landlord_user_id , IF(D.city IS NOT NULL, D.city, DD.city) as landlord_city , 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 ,eventdata_totaltransactionamount as amt ,eventdata_senderglobalcardid as CC from foxtrot_stream.payment_backend_transact where 0 = 0 and year = 2022 and month = 8 AND day = 2 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 INNER JOIN (SELECT DISTINCT user_id, city FROM edw_shared.user_dimension WHERE city IS NOT NULL)B ON A.sender = B.user_id LEFT JOIN (SELECT DISTINCT user_ext_id AS user_id, phone_number FROM users.users WHERE blacklisted = 0)C ON A.landlord = C.phone_number LEFT JOIN (SELECT DISTINCT user_ext_id AS user_id, primary_vpa FROM users.users WHERE blacklisted = 0)CC ON A.landlord = CC.primary_vpa LEFT JOIN (SELECT DISTINCT user_id, city FROM edw_shared.user_dimension WHERE city IS NOT NULL)D ON C.user_id = D.user_id LEFT JOIN (SELECT DISTINCT user_id, city FROM edw_shared.user_dimension WHERE city IS NOT NULL)DD ON CC.user_id = DD.user_id) SELECT A.sender, B.landlords, B.tot_rent_paid, B.CC_used, B.tot_sent_loc, B.landlords_from_another_city FROM (SELECT DISTINCT sender FROM base WHERE sender_city <> landlord_city)A INNER JOIN (SELECT sender, COUNT(landlord) as landlords, SUM(amt) as tot_rent_paid, COUNT(DISTINCT CC) AS CC_used , COUNT(DISTINCT landlord_city) as tot_sent_loc, COUNT(DISTINCT IF(sender_city <> landlord_city, landlord_city, NULL)) as landlords_from_another_city FROM base GROUP BY sender HAVING landlords > 1 AND tot_rent_paid < 100000 AND CC_used > 1)B ON A.sender = B.sender
Comments