RFI 22

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

--RULE 22
SELECT DATE_SUB('2023-04-02',32) starting_date
,DATE_SUB('2023-04-02',2) ending_date
,receiveruser AS identifier
,active_days as active_days
,perc AS value
,'RFI22 'AS red_flag
,'monthly' as date_range
,'AML' `group`
,'FRA' `type`
,'Alerts' type_fra
,'User' issue_type
,'UPI' sub_issue_type
,CONCAT('total_block_amt: ', total_block_amt, ' , total_block_txns: ', total_block_txns, ' , success_amt: ', success_amt, ' , success_txns: ', success_txns) AS comment FROM
    (SELECT receiveruser
	, COUNT (DISTINCT int_block_txn_id) int_blocked_txns
	, SUM (int_block_txn_amt) int_blocked_amt
	, COUNT (DISTINCT network_block_txn_id) network_block_txns
	, SUM (network_block_txn_amt) network_block_amt
	, SUM (success_amount) success_amt
	, COUNT (DISTINCT success_txn_id) success_txns
	, COUNT (DISTINCT int_block_txn_id)+COUNT (DISTINCT network_block_txn_id) AS total_block_txns
	, (SUM (int_block_txn_amt) + SUM (network_block_txn_amt)) total_block_amt
	, ((SUM (int_block_txn_amt) + SUM (network_block_txn_amt))-SUM (success_amount))/SUM (success_amount) AS perc
	, COUNT(DISTINCT updated_date) as active_days
    FROM
        (SELECT A.receiveruser, A.transaction_id, A.totaltransactionamount
        , (CASE WHEN A.backend_errorcode = 'SUCCESS' AND A.errorcode = 'SUCCESS' AND A.pay_transaction_status = 'COMPLETED' THEN A.transaction_id END) success_txn_id
	    , (CASE WHEN A.backend_errorcode = 'SUCCESS' AND A.errorcode = 'SUCCESS' AND A.pay_transaction_status = 'COMPLETED' THEN A.totaltransactionamount END) success_amount
	    , B.eventdata_referenceid AS int_block_txn_id, B.eventdata_requestcontext_totaltransactionamount AS int_block_txn_amt
	    , C.transaction_id as network_block_txn_id, C.amount as network_block_txn_amt
	    , A.updated_date FROM
	        (SELECT receiveruser, transaction_id, backend_errorcode, errorcode, pay_transaction_status, totaltransactionamount, updated_date
	        from fraud.transaction_details_v3
	        where updated_date between DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2))A
	    LEFT JOIN
	    	(SELECT eventdata_referenceid, eventdata_requestcontext_totaltransactionamount
	    	FROM foxtrot_stream.kratos_default
	    	WHERE year >= YEAR(DATE_SUB('2023-04-02',32)) AND year <= YEAR(DATE_SUB('2023-04-02',2)) 
	    	and month >= MONTH(DATE_SUB('2023-04-02',32)) AND month <= MONTH(DATE_SUB('2023-04-02',2)) 
	    	AND eventdata_appliedmeta_actions = 'BLOCK' AND eventdata_actions = 'BLOCK'
	    	AND eventdata_appliedmeta_mode = 'LIVE' AND eventdata_tenant = 'payment'
	    	AND eventdata_sourceeventtype IN ('payment_PAY|CONSUMER_TO_MERCHANT' , 'payment_PAY|EXTERNAL_TO_MERCHANT')
	    	AND eventdata_picked = 1)B
        ON A.transaction_id = B.eventdata_referenceid
        LEFT JOIN
        	(SELECT transaction_id, amount
        	from payment.transactions
        	where year >= YEAR(DATE_SUB('2023-04-02',32)) AND year <= YEAR(DATE_SUB('2023-04-02',2)) 
	    	and month >= MONTH(DATE_SUB('2023-04-02',32)) AND month <= MONTH(DATE_SUB('2023-04-02',2)) 
            and updated between DATE_SUB('2023-04-02',32) and DATE_SUB('2023-04-02',2)
            and state = 'FAILED'
            and (substring(backend_error_code, 1, 3) in ('U16','M16')
            or backend_error_code in ('ZI','K1','DECLINED_BY_RISK','SUSPECTED_FRAUD','DECLINED_BY_ISSUER','TXN_DECLINED','DO_NOT_HONOUR','CARD_DECLINED','SUSPECTED_FRAUD'
            ,'AUTHORIZATION_FAILED','STOLEN_LOST_CARD','TXN_NOT_ALLOWED','CARD_NOT_ALLOWED','BLOCKED_CARD','DECLINED_BY_RISK','CARD_VALIDATION_FAILURE')))C
        ON A.transaction_id = C.transaction_id )P
    GROUP BY receiveruser
    HAVING (SUM (int_block_txn_amt) + SUM (network_block_txn_amt)) > SUM (success_amount)
    AND (SUM (int_block_txn_amt) + SUM (network_block_txn_amt)) > 150000000
    AND (COUNT (DISTINCT int_block_txn_id)+COUNT (DISTINCT network_block_txn_id)) > 100
    AND ((SUM (int_block_txn_amt) + SUM (network_block_txn_amt))-SUM (success_amount))/SUM (success_amount) > 0.6
    ORDER BY perc desc)X