RFI 20

PHOTO EMBED

Mon May 08 2023 11:04:38 GMT+0000 (Coordinated Universal Time)

Saved by @shubhangi_burle 

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

--RFI 20
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
,perc AS value
,'RFI20' AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('fraud_txns: ', fraud_txns, ' , fraud_amount: ', fraud_amount, ' , success_txns: ', success_txns, ' , success_amount: ', success_amount) AS comment
FROM
    (SELECT receiveruser, fraud_txns, fraud_amount, success_txns, success_amount, (fraud_amount/success_amount) as perc, active_days FROM
        (SELECT receiveruser, count(distinct transaction_id) as success_txns, sum(totaltransactionamount) as success_amount, COUNT(DISTINCT updated_date) as active_days
        from fraud.transaction_details_v3
        where updated_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        AND workflowtype IN ('CONSUMER_TO_MERCHANT' , 'EXTERNAL_TO_MERCHANT')
        and (receiversubtype In ('OFFLINE_UNORGANISED','P2P_MERCHANT','P2M_LIMITED') or origination_mode = 'B2B_PG')
        AND backend_errorcode = 'SUCCESS' AND pay_transaction_status= 'COMPLETED'
        group by receiveruser)A
    INNER JOIN
        (SELECT receiveruserid, COUNT(DISTINCT transaction_id) as fraud_txns, SUM(fraud_amt_rs) as fraud_amount
        from fraud.mark_transaction_details_v2
        where date_tx between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        GROUP BY receiveruserid)B
    ON A.receiveruser = B.receiveruserid
    where (fraud_amount/success_amount) > 0.5 and fraud_txns > 3
    order by fraud_amount desc)X
content_copyCOPY