RFI 51

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

--RFI 51 WALLET
SELECT DATE_SUB('2023-05-02',32) starting_date
,DATE_SUB('2023-05-02',2) ending_date
,senderuserid AS identifier
,active_days as active_days
,fraud_amt AS value
,CONCAT('RFI51 ', instrument_type) AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('receiver_count: ', receiver_count, ' , fraud_txns: ', fraud_txns, ' , success_txns: ', success_txns, ' , success_amt: ', success_amt) AS comment
FROM
    (SELECT A.senderuserid, B.receiver_count, A.instrument_type, A.fraud_txns, A.fraud_amt, B.success_txns, B.success_amt, active_days FROM
        (SELECT senderuserid, instrument_type, count(distinct transaction_id) fraud_txns, sum(fraud_amt_rs) fraud_amt
        from fraud.mark_transaction_details_v2
        where mark_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        and instrument_type = 'WALLET'
        GROUP BY senderuserid, instrument_type) A
    LEFT JOIN
        (SELECT senderuserid, count(distinct receiveruser) receiver_count, sum(totaltransactionamount) success_amt
        , count(distinct transaction_id) success_txns, 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 backend_errorcode = 'SUCCESS' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
        AND wallet_flag = true
        GROUP BY senderuserid)B
    ON A.senderuserid = B.senderuserid
    WHERE fraud_amt >= 10000 or fraud_txns > 5
    ORDER BY A.fraud_amt desc, A.fraud_txns DESC)X
    
-------------------------------------------------
      
%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;

--RFI 51 EGV
SELECT DATE_SUB('2023-05-02',32) starting_date
,DATE_SUB('2023-05-02',2) ending_date
,senderuserid AS identifier
,active_days as active_days
,fraud_amt AS value
,CONCAT('RFI51 ', instrument_type) AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'Merchant' issue_type
,'UPI' sub_issue_type
,CONCAT('receiver_count: ', receiver_count, ' , fraud_txns: ', fraud_txns, ' , success_txns: ', success_txns, ' , success_amt: ', success_amt) AS comment
FROM
    (SELECT A.senderuserid, B.receiver_count, A.instrument_type, A.fraud_txns, A.fraud_amt, B.success_txns, B.success_amt, active_days FROM
        (SELECT senderuserid, instrument_type
        , count(distinct transaction_id) fraud_txns, sum(fraud_amt_rs) fraud_amt
        from fraud.mark_transaction_details_v2
        where mark_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        and instrument_type = 'EGV'
        GROUP BY senderuserid, instrument_type)A
    LEFT JOIN
        (SELECT senderuserid,count(distinct receiveruser) receiver_count, sum(totaltransactionamount) success_amt
        , count(distinct transaction_id) success_txns, 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 backend_errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND errorcode = 'SUCCESS'
        AND upi_flag = false and card_flag = false and wallet_flag = false and freecharge_flag = false and jio_flag = false and airtel_flag = false
        GROUP BY senderuserid)B
    ON A.senderuserid = B.senderuserid
    WHERE fraud_amt > 5000 or fraud_txns > 5
    ORDER BY A.fraud_amt desc, A.fraud_txns DESC)X