RFI 9 NEW & OPTIMIZED

%jdbc(hive)
set tez.queue.name=fra_analytics;
set hive.execution.engine=tez;

--RULE 9 OPTIMIZED & B2BPG
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', 'ONLINE_MERCHANT'))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', 'ONLINE_MERCHANT'))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', 'ONLINE_MERCHANT')
        AND (origination_mode = 'B2B_PG' OR origination_mode IS NULL)
        Group by receiveruser
        HAVING sum(totaltransactionamount) > 300000)B
    ON C.merchant_id = B.receiveruser
    ORDER BY B.amount DESC)X