RFI 1

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

-- RFI 1
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
,max_for_current_month AS value
,'RFI1' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'UPI' sub_issue_type
,CONCAT('max_for_previous_month: ', max_for_previous_month, ' , percent_jump: ', percent_jump, ' , final_merchant_classification: ', final_merchant_classification ) AS comment
from
    (select A.receiveruser, A.max_for_previous_month, B.max_for_current_month, ((B.max_for_current_month - A.max_for_previous_month )/ B.max_for_current_month) as percent_jump
    ,C.final_merchant_classification, B.active_days from
        (Select receiveruser, max(totaltransactionamount) as max_for_previous_month
        from fraud.transaction_details_v3
        where updated_date between DATE_SUB('2023-04-02',33+30) and DATE_SUB('2023-04-02',33) and receivertype = 'MERCHANT' 
        and receiversubtype In ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') 
        and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
        group by receiveruser
        having max(totaltransactionamount) > 24000)A
    INNER join
        (Select receiveruser , max(totaltransactionamount) as max_for_current_month, COUNT(DISTINCT updated_date) as active_days
        from fraud.transaction_details_v3
        where updated_date between DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2)
        and receivertype = 'MERCHANT' and receiversubtype In ('OFFLINE_UNORGANISED','OFFLINE_THROUGH_AGGREGATOR','P2P_MERCHANT','P2M_LIMITED','OFFLINE_AGGREGATOR') 
        and errorcode = 'SUCCESS' and backend_errorcode = 'SUCCESS' and pay_transaction_status = 'COMPLETED'
        group by receiveruser
        having max(totaltransactionamount) > 24000)B
    on A.receiveruser = B.receiveruser
    inner join
        (select merchant_id, final_merchant_classification
        from fraud.fra_rbi_hml_final
        WHERE final_merchant_classification = 'H'
        group by merchant_id, final_merchant_classification)C
    ON B.receiveruser = C.merchant_id
    where ((B.max_for_current_month - A.max_for_previous_month )/ B.max_for_current_month ) > 0.75 
    group by A.receiveruser, A.max_for_previous_month, B.max_for_current_month, C.final_merchant_classification, B.active_days)X