How to Detect Database Anomalies in Transaction Amounts in SQL
This query detects anomalies in transaction amounts, where a transaction is considered an anomaly if its amount is more than twice the standard deviation away from the average transaction amount.
WITH
stats AS (
SELECT
AVG(amount) AS avg_amount,
STDDEV (amount) AS stddev_amount
FROM
transactions
)
SELECT
t.transaction_id,
t.amount
FROM
transactions t,
stats s
WHERE
t.amount>s.avg_amount+2*s.stddev_amount
OR t.amount<s.avg_amount - 2*s.stddev_amount;