6 MONTHS CONSISTENCY: TOP 100 LANDLORDS

%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.day
    , A.month
    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
        from foxtrot_stream.payment_backend_transact
        where 0 = 0
        AND year = 2022 --year(date_sub ('2022-11-03', 15)) 
        AND month >= 8 -- month(date_sub ('2022-11-03', 15))
        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 --year(date_sub ('2022-11-03', 15))
        AND Month(updated_date) >= 8 --  month(date_sub ('2022-11-03', 15))
        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 B.identifier
, SUM(IF(B.month = 8, B.tenants, 0)) as AugTenants
, SUM(IF(B.month = 8, B.totRentAmt, 0)) as AugAmt
, SUM(IF(B.month = 9, B.tenants, 0)) as SeptTenants
, SUM(IF(B.month = 9, B.totRentAmt, 0)) as SeptAmt
, SUM(IF(B.month = 10, B.tenants, 0)) as OctTenants
, SUM(IF(B.month = 10, B.totRentAmt, 0)) as OctAmt
, SUM(IF(B.month = 11, B.tenants, 0)) as NovTenants
, SUM(IF(B.month = 11, B.totRentAmt, 0)) as NovAmt
FROM
    (SELECT identifier, COUNT(DISTINCT month) as monthsReceived FROM
        (SELECT landlord_user_id as identifier
        , month
        , COUNT(DISTINCT sender) as tenants
        FROM base
        WHERE landlord_user_id IS NOT NULL
        GROUP BY landlord_user_id, month
        HAVING tenants > 3)X
    GROUP BY identifier
    HAVING monthsReceived = 4)A
INNER JOIN
    (SELECT landlord_user_id as identifier
    , month
    , COUNT(DISTINCT sender) as tenants
    , SUM(amt) as totRentAmt
    FROM base
    WHERE landlord_user_id IS NOT NULL
    GROUP BY landlord_user_id, month
    HAVING tenants > 3)B
ON A.identifier = B.identifier
GROUP BY B.identifier
ORDER BY AugTenants DESC, AugAmt DESC LIMIT 100