RFI 22

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

--RFI 22
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
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 FROM
	    (SELECT receiveruser, transaction_id, backend_errorcode, errorcode, pay_transaction_status, totaltransactionamount
	    from fraud.transaction_details_v3
	    where updated_date between DATE_SUB('2023-04-02', 32) and DATE_SUB('2023-04-02', 2) AND receivertype = 'MERCHANT')A
	LEFT JOIN
		(SELECT eventdata_referenceid, eventdata_requestcontext_totaltransactionamount
		FROM foxtrot_stream.kratos_default
		WHERE year IN (YEAR(DATE_SUB('2023-04-02', 32)), YEAR(DATE_SUB('2023-04-02', 2)))
		AND month IN (MONTH(DATE_SUB('2023-04-02', 32)), MONTH(DATE_SUB('2023-04-02', 2)))
		and day >= DAY(DATE_SUB('2023-04-02', 32)) AND day > DAY(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 IN (YEAR(DATE_SUB('2023-04-02', 32)), YEAR(DATE_SUB('2023-04-02', 2)))
		AND month IN (MONTH(DATE_SUB('2023-04-02', 32)), 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)) > 1500000
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