WALLET MX TXN AMT PER TXN
Mon Nov 21 2022 11:43:12 GMT+0000 (Coordinated Universal Time)
Saved by @shubhangi_burle
SELECT '2022' AS year, P.monthNo, P.kyc, P.catg , 'PerTxn' as Frequency, P.breaches, 'WalletMxTxnAmtPerTxn' as rule FROM (SELECT Z.catg, Z.kyc, Z.monthNo, COUNT(DISTINCT Z.transaction_id) AS breaches FROM (SELECT A.senderuserid, month(A.TxnTime) as monthNo, A.transaction_id, B.amt ,IF(C.hml_category IS NOT NULL, C.hml_category, 'LOW RISK') AS catg, D.kyc, CASE WHEN C.hml_category = 'HIGH RISK' AND (D.kyc = 'FULL' OR D.kyc IS NULL) THEN IF(5000 > (B.balance * 0.7), 5000, (B.balance * 0.7)) WHEN C.hml_category = 'MEDIUM RISK' AND (D.kyc = 'FULL' OR D.kyc IS NULL) THEN IF(5000 > (B.balance * 0.8), 5000, (B.balance * 0.8)) WHEN C.hml_category IS NULL AND (D.kyc = 'FULL' OR D.kyc IS NULL) THEN 10000 WHEN C.hml_category = 'HIGH RISK' AND D.kyc = 'MIN' THEN IF(10000 > (B.balance * 0.7), 10000, (B.balance * 0.7)) WHEN C.hml_category = 'MEDIUM RISK' AND D.kyc = 'MIN' THEN IF(15000 > (B.balance * 0.8), 15000, (B.balance * 0.8)) WHEN C.hml_category IS NULL AND D.kyc = 'MIN' THEN 20000 END AS limit FROM (SELECT DISTINCT senderuserid, transaction_time as TxnTime, transaction_id FROM fraud.transaction_details_v3 WHERE Year(updated_date) = 2022 AND month(updated_date) = 11 AND workflowtype = 'CONSUMER_TO_MERCHANT' and sendertype = 'INTERNAL_USER' AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED' AND wallet_flag = 'TRUE' AND totaltransactionamount >= 5000)A INNER JOIN (SELECT DISTINCT txn_id, amount/100 as amt, (amount + closing_available_balance)/100 as balance FROM wallet.transaction_master WHERE year = 2022 AND month = 11 AND category = 'ORDER' AND txn_type = 'DEBIT' AND txn_state = 'SUCCESS' AND response_code = 'SUCCESS' AND amount/100 >= 5000)B LEFT JOIN (SELECT DISTINCT senderuserid, hml_category FROM fraud.hml_classification)C ON A.senderuserid = C.senderuserid LEFT JOIN (SELECT DISTINCT user_ext_id, IF(kyc in ('MIN_KYC', 'MIN_KYC_V2'), 'MIN', 'FULL') as kyc FROM users.users)D ON A.senderuserid = D.user_ext_id)Z WHERE Z.amt >= Z.limit GROUP BY Z.catg, Z.kyc, Z.monthNo)P
Comments