%jdbc(hive) set tez.queue.name=fra_analytics; set hive.execution.engine=tez; -- RULE 10 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 ,totAmt AS value ,'RFI10' AS red_flag ,'monthly' as date_range ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'UPI' sub_issue_type ,STRING(txn_count) AS comment FROM (SELECT A.receiveruser, A.totAmt, A.txn_count, A.active_days, C.merchant_acquisition_date, C.merchant_activation_date FROM (SELECT receiveruser, SUM(totaltransactionamount) as totAmt, count(distinct transaction_id) as txn_count, COUNT(DISTINCT updated_date) as active_days FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN DATE_SUB('2023-04-02', 33+30) AND DATE_SUB('2023-04-02',33) AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND receivertype = 'MERCHANT' GROUP BY receiveruser)A LEFT JOIN (SELECT DISTINCT receiveruser FROM fraud.transaction_details_v3 WHERE updated_date BETWEEN DATE_SUB('2023-04-02',32) AND DATE_SUB('2023-04-02',2) AND backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND receivertype = 'MERCHANT')B ON A.receiveruser = B.receiveruser INNER JOIN (SELECT merchant_id, merchant_acquisition_date, merchant_activation_date FROM fraud.fra_merchant_dimension WHERE merchant_acquisition_date BETWEEN DATE_SUB('2023-04-02', 33+30) AND DATE_SUB('2023-04-02',33) AND merchant_activation_date BETWEEN DATE_SUB('2023-04-02', 33+30) AND DATE_SUB('2023-04-02',33) AND blacklisted = 0)C ON A.receiveruser = C.merchant_id WHERE B.receiveruser IS NULL AND A.totAmt > 200000)X