Monthly ALERT: top 5 landlords based on highest tenants cnt
Thu Nov 03 2022 07:57:12 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
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 = year(date_sub ('2022-11-03', 15))
AND month = 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) = year(date_sub ('2022-11-03', 15))
AND Month(updated_date) = 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 DATE_SUB('2022-11-03',34) starting_date, DATE_SUB('2022-11-03',4) ending_date
,A.identifier identifier
,active_days active_days
,'top_landlord_highest_tenants' red_flag
,'monthly' date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'CC' sub_issue_type
,A.tenants comment
,A.totRentAmt value
,(case when B.identifier is null then 'New' else 'Repeat' end) as new_or_repeat
,'2022-11-03' run_date FROM
(SELECT landlord_user_id as identifier
, month
, COUNT(DISTINCT sender) as tenants
, SUM(amt) as totRentAmt
, COUNT(DISTINCT day) as active_days
, row_number() OVER (PARTITION BY month ORDER BY COUNT(DISTINCT sender) DESC, SUM(amt) DESC) rn
FROM base
WHERE landlord_user_id IS NOT NULL
GROUP BY landlord_user_id, month
HAVING tenants > 1)A
LEFT JOIN
(select DISTINCT identifier from fraud.aml_freshdesk_month)B
on A.identifier = B.identifier
WHERE A.rn <= 5
-----------------------------------------------------------------------------
%jdbc(hive)
set tez.queue.name=default;
set hive.execution.engine=tez;
-- active in last 3 months too
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, A.year
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, year
from foxtrot_stream.payment_backend_transact
where 0 = 0
AND year IN (year(date_sub ('2022-12-03', 15)), year(date_sub ('2022-12-03', 45)), year(date_sub ('2022-12-03', 75)), year(date_sub ('2022-12-03', 105)))
AND month IN (month(date_sub ('2022-12-03', 15)), month(date_sub ('2022-12-03', 45)), month(date_sub ('2022-12-03', 75)), month(date_sub ('2022-12-03', 105)))
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) IN (year(date_sub ('2022-12-03', 15)), year(date_sub ('2022-12-03', 45)), year(date_sub ('2022-12-03', 75)), year(date_sub ('2022-12-03', 105)))
AND Month(updated_date) IN (month(date_sub ('2022-12-03', 15)), month(date_sub ('2022-12-03', 45)), month(date_sub ('2022-12-03', 75)), month(date_sub ('2022-12-03', 105)))
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 DATE_SUB('2022-12-03',34) starting_date, DATE_SUB('2022-12-03',4) ending_date
,A.identifier identifier
,A.active_days active_days
,'top_landlord_highest_tenants' red_flag
,'monthly' date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'CC' sub_issue_type
,A.tenants comment
,A.totRentAmt value
,(case when (B.identifier is null AND D.identifier is null) then 'New' else 'Repeat' end) as new_or_repeat
,'2022-12-03' run_date FROM
(SELECT landlord_user_id as identifier
, month
, COUNT(DISTINCT sender) as tenants
, SUM(amt) as totRentAmt
, COUNT(DISTINCT day) as active_days
, row_number() OVER (PARTITION BY month ORDER BY COUNT(DISTINCT sender) DESC, SUM(amt) DESC) rn
FROM base
WHERE year = year(date_sub ('2022-12-03', 15)) AND month = month(date_sub ('2022-12-03', 15))
AND landlord_user_id IS NOT NULL
GROUP BY landlord_user_id, month
HAVING tenants > 3)A
INNER JOIN
(SELECT identifier, COUNT(DISTINCT month) as months FROM
(SELECT landlord_user_id as identifier
, month
, COUNT(DISTINCT sender) as tenants
FROM base
WHERE year IN (year(date_sub ('2022-12-03', 105)), year(date_sub ('2022-12-03', 45)), year(date_sub ('2022-12-03', 75)))
AND month IN (month(date_sub ('2022-12-03', 105)), month(date_sub ('2022-12-03', 45)), month(date_sub ('2022-12-03', 75)))
AND landlord_user_id IS NOT NULL
GROUP BY landlord_user_id, month)X
WHERE tenants > 3
GROUP BY identifier
HAVING months = 3)C
ON A.identifier = C.identifier
LEFT JOIN
(select DISTINCT identifier from fraud.aml_freshdesk_month)B
on A.identifier = B.identifier
LEFT JOIN
(select DISTINCT identifier from fraud.aml_freshdesk)D
on A.identifier = D.identifier
WHERE A.rn <= 5
Comments