WALLET MX TXN AMT MONTHLY

SELECT '2022' AS year, monthNo, kyc, catg as risk_catg, 'Monthly' as Frequency, breaches, 'WalletMxTxnAmtMonthly' as rule FROM
  (SELECT Z.catg, Z.kyc, COUNT(DISTINCT Z.transaction_id) AS breaches, Z.monthNo FROM
        (SELECT A.senderuserid, A.transaction_id, month(A.TxnTime) as monthNo, (SUM(B.amt) + A.amt) as monthlyAmt
        , 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 100000
        WHEN C.hml_category = 'MEDIUM RISK' AND (D.kyc = 'FULL' OR D.kyc IS NULL) THEN 100000
        WHEN C.hml_category IS NULL AND (D.kyc = 'FULL' OR D.kyc IS NULL) THEN 100000 
        WHEN C.hml_category = 'HIGH RISK' AND D.kyc = 'MIN' THEN 10000 
        WHEN C.hml_category = 'MEDIUM RISK' AND D.kyc = 'MIN' THEN 10000
        WHEN C.hml_category IS NULL AND D.kyc = 'MIN' THEN 10000
        END AS limit FROM
          (SELECT DISTINCT senderuserid, transaction_time as TxnTime, transaction_id, totaltransactionamount as amt
          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')A
        LEFT JOIN
          (SELECT DISTINCT senderuserid, transaction_time as TxnTime, transaction_id, totaltransactionamount as amt
          FROM fraud.transaction_details_v3
          WHERE Year(updated_date) = 2022 AND month(updated_date) IN (11-1, 11)
          AND workflowtype = 'CONSUMER_TO_MERCHANT' and sendertype = 'INTERNAL_USER' 
          AND errorcode = 'SUCCESS' AND pay_transaction_status = 'COMPLETED'
          AND wallet_flag = 'TRUE')B
        ON A.senderuserid = B.senderuserid AND ((UNIX_TIMESTAMP(A.txnTime) - UNIX_TIMESTAMP(B.txnTime))/2592000) BETWEEN 0 AND 1
        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
        GROUP BY A.senderuserid, A.transaction_id, month(A.TxnTime), C.hml_category, D.kyc, A.amt)Z
  WHERE Z.monthlyAmt >= Z.limit
  GROUP BY Z.catg, Z.kyc, Z.monthNo)P