RFI 1 NEW (optimized & b2bpg)

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

-- RFI 1
select DATE_SUB('2023-05-02',32) starting_date
,DATE_SUB('2023-05-02',2) ending_date
,receiveruser AS identifier
,max_for_current_month AS value
, current_active_days AS active_days
,'RFI1' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('max_for_previous_month: ', max_for_previous_month, ' , percent_jump: ', percent_jump) AS comment
from
    (select A.receiveruser, A.max_for_previous_month, A.max_for_current_month, ((A.max_for_current_month - A.max_for_previous_month )/ A.max_for_current_month) as percent_jump
    , A.current_active_days from
        (Select receiveruser
        , max(CASE WHEN updated_date between DATE_SUB('2023-05-02',33+30) and DATE_SUB('2023-05-02',33) THEN totaltransactionamount ELSE 0 END) as max_for_previous_month
        , max(CASE WHEN updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END) as max_for_current_month
        , COUNT(DISTINCT CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN updated_date ELSE NULL END) AS current_active_days
        from fraud.transaction_details_v3
        where updated_date between DATE_SUB('2023-05-02',33+30) and DATE_SUB('2023-05-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 max(CASE WHEN updated_date between DATE_SUB('2023-05-02',33+30) and DATE_SUB('2023-05-02',33) THEN totaltransactionamount ELSE 0 END) > 24000
        AND max(CASE WHEN updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END) > 24000
        AND ((max(CASE WHEN updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END)
        - max(CASE WHEN updated_date between DATE_SUB('2023-05-02',33+30) and DATE_SUB('2023-05-02',33) THEN totaltransactionamount ELSE 0 END))
        / max(CASE WHEN updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END)) > 0.75)A
    inner join
        (select merchant_id
        from fraud.fra_rbi_hml_final
        WHERE final_merchant_classification = 'H'
        GROUP BY merchant_id)C
    ON A.receiveruser = C.merchant_id
    group by A.receiveruser, A.max_for_previous_month, A.max_for_current_month, A.current_active_days)X