RFI 2

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

--rfi 2
SELECT DATE_SUB('2023-05-02',32) starting_date
,DATE_SUB('2023-05-02',2) ending_date
,receiveruser AS identifier
,current_active_days as active_days
,percent_increase_amount AS value
,'RFI2' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('sender_count: ', previous_txn_cnt, ' , current_txn_cnt: ', current_txn_cnt, ' , 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.current_amount_tol - A.previous_amount_tol)/A.previous_amount_tol) as percent_increase_amount
    , A.previous_amount_tol, A.current_amount_tol, A.current_active_days
    FROM
        (SELECT merchant_id
        from merchant_onboarding.merchants
        where merchant_type IN ('OFFLINE_UNORGANISED','P2P_MERCHANT','P2M_LIMITED') 
        and date(onboarded_at) < DATE_SUB('2023-05-02',33+30+120) )C
    INNER JOIN
        (SELECT receiveruser
        , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02',33+30) and DATE_SUB('2023-05-02',33) THEN transaction_id ELSE NULL END) as previous_txn_cnt
        , sum(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 previous_amount_tol 
        , count(distinct CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN transaction_id ELSE NULL END) as current_txn_cnt 
        , sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END) as current_amount_tol 
        , 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','P2P_MERCHANT','P2M_LIMITED') or origination_mode in ('B2B_PG')) 
        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-05-02',33+30) and DATE_SUB('2023-05-02',33) THEN totaltransactionamount ELSE 0 END) > 250000
        AND sum(CASE WHEN updated_date BETWEEN DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) THEN totaltransactionamount ELSE 0 END) > 250000 )A
    ON C.merchant_id = A.receiveruser
    where (A.current_txn_cnt/A.current_active_days) >= 300 
    and ((A.current_amount_tol - A.previous_amount_tol)/A.previous_amount_tol) > 0.5 
    and ((A.current_txn_cnt-A.previous_txn_cnt)/A.previous_txn_cnt) > 0.5)X