RFI 10

%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