RFI 27

%jdbc(hive)
set tez.queue.name = fra_analytics;
set hive.execution.engine=tez;
set hive.fetch.task.conversion=none;
SET hive.tez.container.size=24576;
SET hive.tez.java.opts=-Xmx20480m;
set hive.exec.orc.skip.corrupt.data=true;

--rfi 27
SELECT DATE_SUB('2023-05-02',32) starting_date
,DATE_SUB('2023-05-02',2) ending_date
,receiveruser AS identifier
,active_days as active_days
,round_value_txn AS value
,'RFI27' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('sender_count: ', sender_count, ' , sender_state: ', sender_state, ' , total_amount: ', total_amount, ' , txn_cnt: ', txn_cnt, ' , onboarding_date: ', onboarding_date) AS comment
FROM
    (SELECT A.receiveruser, A.sender_count, A.sender_state, A.total_amount, A.txn_cnt, B.onboarding_date, A.round_value_txn, A.active_days from
        (select merchant_id, date(onboarded_at) as onboarding_date
        from merchant_onboarding.merchants
        where (date(onboarded_at)) > DATE_SUB('2023-05-02',32+30)
        group by merchant_id, onboarded_at)B
    left join
        (SELECT receiveruser, count(distinct sender_vpa) as sender_count, count(distinct sender_state) as sender_state, sum(totaltransactionamount) as total_amount
        , count(distinct transaction_id) as txn_cnt, COUNT(DISTINCT updated_date) as active_days
        , count(distinct(case when (totaltransactionamount % 100 = 0) then transaction_id else 0 end)) as round_value_txn
        from fraud.transaction_details_v3
        where updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        and (receiversubtype In ('OFFLINE_UNORGANISED','P2P_MERCHANT','P2M_LIMITED') or origination_mode in ('B2B_PG'))
        and backend_errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
        group by receiveruser)A
    on B.merchant_id = A.receiveruser
    group by A.receiveruser, A.sender_count, A.sender_state, A.total_amount, A.txn_cnt,B.onboarding_date, A.round_value_txn, A.active_days
    having A.sender_state > 10 and A.sender_count > 500 and A.txn_cnt > 1000 
    and ((A.total_amount/ A.txn_cnt) between 100 and 300) and (A.round_value_txn/A.txn_cnt) > 0.35)X