RFI 3 NEW & OPTIMIZED

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

-- RFI 3
select DATE_SUB('2023-04-02',32) starting_date
,DATE_SUB('2023-04-02',2) ending_date
,receiveruser AS identifier
,current_active_days as  active_days
,current_txn_cnt AS value
,'RFI3' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('previous_txn_cnt: ', previous_txn_cnt, ' , percent_increase_count: ', percent_increase_count, ' , previous_amount_tol: ', previous_amount_tol, ' , current_amount_tol: ', current_amount_tol) AS comment
from
    (
    SELECT A.receiveruser, A.previous_txn_cnt, A.current_txn_cnt, A.previous_amount_tol, A.current_amount_tol
    , ((A.current_txn_cnt - A.previous_txn_cnt)/A.previous_txn_cnt) as percent_increase_count
    , A.current_active_days FROM
        (SELECT merchant_id
        from merchant_onboarding.merchants
        where merchant_type IN ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR', 'ONLINE_MERCHANT') 
        and date(onboarded_at) < DATE_SUB('2023-04-02',33+30+60))C
    INNER JOIN
        (
        SELECT receiveruser
        , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) THEN transaction_id END)-1 as previous_txn_cnt
        , sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) THEN totaltransactionamount ELSE 0 END) as previous_amount_tol
        , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) THEN transaction_id END)-1 as current_txn_cnt
        , sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) THEN totaltransactionamount ELSE 0 END) as current_amount_tol
        , COUNT(DISTINCT CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) THEN updated_date END)-1 AS current_active_days
        from fraud.transaction_details_v3
        where updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and  DATE_SUB('2023-04-02',2)
        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)
        and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
        group by receiveruser
        having sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) THEN totaltransactionamount ELSE 0 END) >100000
        AND sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) THEN totaltransactionamount ELSE 0 END) >100000
        AND ((count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2) THEN transaction_id END)
        - count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) THEN transaction_id END))
        / count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) THEN transaction_id END)) >= 20
        )A
    ON C.merchant_id = A.receiveruser
    )X