WALLET MX TXN AMT PER TXN

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