%jdbc(hive)
set tez.queue.name=fra_analytics;
set hive.execution.engine=tez;
--RULE 9
SELECT DATE_SUB('2023-04-02',32) starting_date
,DATE_SUB('2023-04-02',2) ending_date
,receiveruser AS identifier
,Active_days as active_days
,amount AS value
,'RFI9' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'UPI' sub_issue_type
,CONCAT('Onboarded: ', onboarded_at, ' , last_txn_date: ', last_txn_date, ' , blacklisted_date: ', blacklisted_date ) AS comment
FROM
(SELECT B.receiveruser, B.amount, B.no_of_txns, B.Active_days, B.last_txn_date, C.onboarded_at, A.blacklisted_date FROM
(SELECT merchant_id, blacklisted_date
FROM edw_shared.merchant_store_dimension
WHERE blacklisted_date between DATE_SUB('2023-04-02',32) AND DATE_SUB('2023-04-02',2)
AND blacklisted = 1
AND merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR'))A
INNER JOIN
(SELECT merchant_id, onboarded_at
FROM merchant_onboarding.merchants
WHERE onboarded_at between DATE_SUB('2023-04-02',33+60) and DATE_SUB('2023-04-02',33)
AND merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR'))C
ON A.merchant_id = C.merchant_id
INNER JOIN
(SELECT receiveruser, sum(totaltransactionamount) amount, COUNT (distinct transaction_id) as no_of_txns
, COUNT(distinct updated_date) as Active_days, MAX(updated_date) as last_txn_date
FROM fraud.transaction_details_v3
WHERE updated_date >= DATE_SUB('2023-04-02',33+60)
AND backend_errorcode = 'SUCCESS' AND receivertype = 'MERCHANT'
and receiversubtype IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR')
Group by receiveruser)B
ON C.merchant_id = B.receiveruser
WHERE B.amount > 300000
ORDER BY B.amount DESC)X
Comments