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

--RULE 21
SELECT DATE_SUB('2023-04-02',32) starting_date
,DATE_SUB('2023-04-02',2) ending_date
,senderuserid AS identifier
,active_days as active_days
,ratio_with_total AS value
,CONCAT('RFI21 ', red_flag) AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'UPI' sub_issue_type
,CONCAT('debit_txns: ', debit_txns, ' , credit_txns: ', credit_txns) AS comment FROM
    (SELECT A.senderuserid, A.debit_txns, B.credit_txns, (A.debit_txns+B.credit_txns) as total_txns
    , IF(A.debit_txns > B.credit_txns, 'DEBIT', 'CREDIT') as red_flag
    ,(greatest (A.debit_txns, B.credit_txns) / (A.debit_txns+B.credit_txns)) as ratio_with_total
    , IF(A.debit_txns > B.credit_txns, A.active_days, B.active_days) as active_days  FROM
        (SELECT senderuserid, COUNT (DISTINCT transaction_id) as debit_txns, 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 backend_errorcode = 'SUCCESS' AND transfermode = 'PEER_TO_PEER'
        GROUP BY senderuserid)A
    INNER JOIN
        (SELECT receiveruser, COUNT (DISTINCT transaction_id) as credit_txns, count(distinct sender_vpa) sender_vpa, 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 backend_errorcode = 'SUCCESS' AND transfermode = 'PEER_TO_PEER'
        GROUP BY receiveruser)B
    ON A.senderuserid = B.receiveruser
    WHERE (A.debit_txns+B.credit_txns) > 2000
    AND (A.debit_txns/ (A.debit_txns+B.credit_txns) >0.9 OR B.credit_txns/ (A.debit_txns+B.credit_txns) >0.9)
    AND (A.debit_txns > 3000 OR (B.credit_txns>5000 AND sender_vpa>1000 ))
    GROUP BY A.senderuserid, A.debit_txns, B.credit_txns, A.active_days, B.active_days
    ORDER BY ratio_with_total desc)X