SQL Anomaly Detection — Applied MAD, EWMA, Consecutive Runs

WITH base AS (
  SELECT
    dt, amount,
    AVG(amount)    OVER w AS base_avg,
    STDDEV(amount) OVER w AS base_stddev
  FROM  daily_sales
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING  -- Exclude today: one to five days ago
  )
)
SELECT
  dt, amount,
  ROUND(base_avg, 2) AS base_avg,
  ROUND(base_stddev, 2) AS base_stddev,
  ROUND(
    (amount - base_avg) / NULLIF(base_stddev, 0),  -- Standardize against the historical baseline
    2
  ) AS z_score,
  CASE
    WHEN ABS((amount - base_avg) / NULLIF(base_stddev, 0)) >= 3
    THEN 'anomaly'
    ELSE 'normal'
  END AS anomaly_flag
FROM  base
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. CTE base          → Define the CTE (evaluate the historical window)
  2. Outer query        → Evaluate the z-score and classify anomaly/normal
  3. ORDER BY dt        → Sort and return the output
*/

WITH med AS (                       -- ① Median over the full period
  SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_val
  FROM  daily_sales
),
dev AS (                              -- ② Absolute deviation from the row's median
  SELECT
    s.dt, s.amount, m.median_val,
    ABS(s.amount - m.median_val) AS abs_dev
  FROM  daily_sales s
  CROSS JOIN med m                    -- Expand the one-row median to every row
),
mad AS (                              -- ③ Median of absolute deviations = MAD
  SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) AS mad_val
  FROM  dev
)
SELECT
  d.dt, d.amount, d.median_val, m.mad_val,
  ROUND(
    (0.6745 * (d.amount - d.median_val) / NULLIF(m.mad_val, 0))::numeric,  -- Modified z-score
    2
  ) AS mod_z,
  CASE
    WHEN ABS(0.6745 * (d.amount - d.median_val) / NULLIF(m.mad_val, 0)) > 3.5
    THEN 'anomaly'
    ELSE 'normal'
  END AS flag
FROM  dev d
CROSS JOIN mad m
ORDER BY d.dt;

/*
  Execution order (logical SQL evaluation order):
  1. CTE med    → Define the CTE (aggregate the median)
  2. CTE dev    → Define the CTE (evaluate absolute deviations)
  3. CTE mad    → Define the CTE (aggregate the median deviation)
  4. Outer query → Evaluate the modified z-score and classify anomaly/normal
  5. ORDER BY    → Sort and return the output
*/

WITH RECURSIVE seq AS (        -- Assign a row number (rn)
  SELECT
    dt, amount,
    ROW_NUMBER() OVER (ORDER BY dt) AS rn
  FROM  daily_sales
),
ewma AS (
  -- ① Non-recursive term (anchor): first row has ema = amount
  SELECT rn, dt, amount, amount::numeric AS ema
  FROM  seq
  WHERE rn = 1

  UNION ALL

  -- ② Recursive term: carry forward the previous ema to calculate the current one
  SELECT
    s.rn, s.dt, s.amount,
    0.5 * s.amount + 0.5 * e.ema  -- α=0.5
  FROM  ewma e
  JOIN  seq s ON s.rn = e.rn + 1  -- Move to the next row
)
SELECT
  dt, amount,
  ROUND(ema, 2) AS ewma
FROM  ewma
ORDER BY rn;

/*
  Execution order (logical SQL evaluation order):
  1. CTE seq            → Define the CTE (assign rn with ROW_NUMBER)
  2. CTE ewma (recursive CTE):
     Evaluate the anchor term (starting point)
     Recurse (expand children in order)
     Stop when there are no more rows
  3. Outer query         → Apply ROUND / ORDER BY and return the output
*/

WITH flagged AS (
  -- ① Add the high-level flag
  SELECT dt, amount,
    CASE WHEN amount >= 200 THEN 1 ELSE 0 END AS is_high
  FROM  daily_sales
),
rownums AS (
  -- ② Calculate two row numbers (overall and by is_high)
  SELECT dt, amount, is_high,
    ROW_NUMBER() OVER (ORDER BY dt) AS rn_all,
    ROW_NUMBER() OVER (PARTITION BY is_high ORDER BY dt) AS rn_flag
  FROM  flagged
),
grouped AS (
  -- ③ Calculate the consecutive-run ID (grp) from the row-number difference
  SELECT dt, amount, is_high,
    (rn_all - rn_flag) AS grp
  FROM  rownums
),
runs AS (
  -- ④ Count the length of each island (consecutive run)
  SELECT dt, amount, is_high, grp,
    COUNT(*) OVER (PARTITION BY is_high, grp) AS run_length
  FROM  grouped
)
SELECT
  dt, amount, is_high, run_length,
  CASE
    WHEN is_high = 1 AND run_length >= 3 THEN 'sustained_anomaly'
    WHEN is_high = 1                     THEN 'noise'
    ELSE 'normal'
  END AS alert_type
FROM  runs
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. CTE flagged  → Define the CTE (add the high-level flag)
  2. CTE rownums  → Define the CTE (assign row numbers)
  3. CTE grouped  → Define the CTE (identify islands by row-number difference)
  4. CTE runs     → Define the CTE (count consecutive days per island)
  5. Outer CASE   → Evaluate the classification
  6. ORDER BY dt  → Sort and return the output
*/