Omniscient AI Agents for Real-Time Anomaly Detection

-- AI isolated root cause
SELECT * FROM eu_stripe_logs WHERE error = '3D Secure'

-- AI Agent Generated: Anomaly Detection via Z-Score calculation
-- Dialect: Snowflake
-- Target: Identify regions where hourly revenue deviates > 2.5 standard deviations from the 14-day trailing average.

WITH hourly_revenue AS (
    SELECT 
        DATE_TRUNC('hour', transaction_timestamp) AS txn_hour,
        region_code,
        SUM(amount_usd) AS total_revenue
    FROM enterprise_tenant.core.fact_transactions
    WHERE transaction_timestamp >= DATEADD(day, -14, CURRENT_TIMESTAMP())
      AND status = 'captured'
    GROUP BY 1, 2
),
rolling_stats AS (
    SELECT 
        txn_hour,
        region_code,
        total_revenue,
        AVG(total_revenue) OVER (
            PARTITION BY region_code 
            ORDER BY txn_hour 
            ROWS BETWEEN 336 PRECEDING AND 1 PRECEDING
        ) AS rolling_avg,
        STDDEV(total_revenue) OVER (
            PARTITION BY region_code 
            ORDER BY txn_hour 
            ROWS BETWEEN 336 PRECEDING AND 1 PRECEDING
        ) AS rolling_stddev
    FROM hourly_revenue
)
SELECT 
    txn_hour,
    region_code,
    total_revenue,
    (total_revenue - rolling_avg) / NULLIF(rolling_stddev, 0) AS z_score
FROM rolling_stats
WHERE z_score <= -2.5 
  AND txn_hour >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
ORDER BY z_score ASC;