RFI 22
Wed Apr 26 2023 11:04:17 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
%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
Comments