%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