A moving average alone does not tell you how much variation is normal. Combining it with a moving standard deviation, moving_stddev, creates a dynamic threshold band: normal range = moving average ± k × moving standard deviation.

STDDEV(amount) OVER (
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- STDDEV = sample standard deviation (denominator N-1)
-- A one-row frame → NULL (standard deviation requires at least two points)
-- STDDEV_POP = population standard deviation (denominator N)

From the daily_sales table, calculate each date's sales amount, the moving average over the latest three days as moving_avg, and the moving standard deviation as moving_stddev. Return dt, amount, moving_avg, moving_stddev in ascending dt order. Round the results to two decimal places.

Expected output (ascending dt):

dt=2024-01-01 is NULL because the STDDEV frame contains only one row. Compared with stddev≈10 during the normal period, it expands more than twentyfold to ≈220 during the spike period.

SELECT
  dt,
  amount,
  ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
  ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev  -- Sample standard deviation (N-1)
FROM  daily_sales
WINDOW w AS (                     -- Share a named window definition
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM daily_sales        → Read the rows
  2. WINDOW w AS (...)       → Define the named window
  3. AVG(amount) OVER w      → Evaluate the window function (preserve row count)
  4. STDDEV(amount) OVER w   → Evaluate the window function (preserve row count)
  5. ROUND(..., 2)           → Format the values
  6. SELECT / ORDER BY dt    → Evaluate the columns, sort, and return the output
*/

A z-score, or standardized score, is a dimensionless measure of how many standard deviations the current value lies from the average. In anomaly detection, |z-score| > 2–3 is commonly used as an anomaly threshold.

-- Definition of a z-score
z = (current value - average) / standard deviation

-- SQL implementation based on a moving window
(amount - AVG(amount) OVER w)
  / NULLIF(STDDEV(amount) OVER w, 0)  -- Guard division by zero with NULL

-- |z| > 2 → outer 4.6% of a normal distribution → investigate
-- |z| > 3 → outer 0.3% of a normal distribution → rule-of-thumb anomaly

From the daily_sales table, use the moving average and moving standard deviation over the latest five days to calculate each day's z-score, then assign anomaly_flag as 'anomaly' when |z| ≥ 2 and 'normal' otherwise. Return dt, amount, moving_avg, moving_stddev, z_score, anomaly_flag in ascending dt order. Round z_score to two decimal places.

Expected output (ascending dt):

While the spike remains in the five-day window, stddev expands sharply, making it difficult for z_score to reach 2. This is the spike-contamination problem. In practice, a LAG-style frame that uses the window before the spike is effective; see the explanation.

WITH stats AS (
  SELECT
    dt, amount,
    ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
    ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev
  FROM  daily_sales
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW  -- Latest five days
  )
)
SELECT
  dt, amount, moving_avg, moving_stddev,
  ROUND(
    (amount - moving_avg)
      / NULLIF(moving_stddev, 0),  -- Convert division by zero to NULL
    2
  ) AS z_score,
  CASE
    WHEN ABS(
           (amount - moving_avg) / NULLIF(moving_stddev, 0)
         ) >= 2
    THEN 'anomaly'
    ELSE 'normal'
  END AS anomaly_flag
FROM  stats
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE stats       → Evaluate window functions (preserve row count)
  2. Evaluate outer query   → Evaluate columns (z_score, anomaly_flag)
  3. ORDER BY dt            → Sort and return the output
*/

The interquartile range, or IQR, is a robust anomaly-detection method that resists outliers. While a z-score is contaminated by the outlier itself, IQR uses only the middle 50% of the data, enabling it to calculate a stable threshold even for real-world data with many spikes.

-- Calculate the first quartile (Q1) and third quartile (Q3)
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3

-- IQR = Q3 - Q1
-- Outlier bounds (Tukey's fences)
-- Lower bound: Q1 - 1.5 * IQR
-- Upper bound: Q3 + 1.5 * IQR

From the daily_sales table, calculate Q1, Q3, and IQR over the entire period, then assign outlier_flag as 'outlier' when an amount lies outside [Q1 − 1.5 × IQR, Q3 + 1.5 × IQR] and 'normal' when it lies inside. Return dt, amount, q1, q3, iqr, lower_fence, upper_fence, outlier_flag in ascending dt order.

Expected output (ascending dt):

q1, q3, and iqr have the same value on every row because they are aggregates over the entire period. The value 400 is an outlier because it greatly exceeds upper_fence=119. Although the spike itself affects Q3, its influence is limited.

WITH fences AS (
  SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS q3
  FROM  daily_sales
)
SELECT
  s.dt,
  s.amount,
  f.q1,
  f.q3,
  f.q3 - f.q1                     AS iqr,
  f.q1 - 1.5 * (f.q3 - f.q1)     AS lower_fence,  -- Tukey's lower fence
  f.q3 + 1.5 * (f.q3 - f.q1)     AS upper_fence,  -- Tukey's upper fence
  CASE
    WHEN s.amount < f.q1 - 1.5 * (f.q3 - f.q1)
      OR  s.amount > f.q3 + 1.5 * (f.q3 - f.q1)
    THEN 'outlier'
    ELSE 'normal'
  END                              AS outlier_flag
FROM  daily_sales s
CROSS JOIN fences f            -- Join the same q1/q3 to every row (one-row CTE)
ORDER BY s.dt;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE fences       → Evaluate aggregate functions (Q1, Q3)
  2. FROM daily_sales s      → Read the rows
  3. CROSS JOIN fences f     → Join them (Cartesian product)
  4. SELECT                  → Evaluate columns (lower_fence, upper_fence, flag)
  5. ORDER BY s.dt           → Sort and return the output
*/

Production anomaly detection must monitor not only absolute magnitude but also the percentage change from the previous day, or rate of change. A change from 100 → 500 is a +400% spike: the percentage change is large even when the absolute value is small. Conversely, a large absolute value can be normal when it remains stable.

-- Calculate day-over-day percentage change
(amount - LAG(amount) OVER (ORDER BY dt))
  / NULLIF(LAG(amount) OVER (ORDER BY dt), 0)
  * 100.0  -- Convert to a percentage

-- If the previous value is 0, NULLIF converts it to NULL → prevents division by zero
-- * 100.0 (a floating-point value) prevents integer division

From the daily_sales table, calculate each day's percentage change from the previous day as pct_change, then assign alert_type as 'spike' for a change of +50% or more, 'drop' for −30% or less, and 'normal' otherwise. Return dt, amount, prev_amount, pct_change, alert_type in ascending dt order. Round pct_change to one decimal place.

01-05: +270% spike. 01-06: −85% drop. 01-07: +87% spike.

Expected output (ascending dt):

WITH with_prev AS (
  SELECT
    dt,
    amount,
    LAG(amount) OVER (ORDER BY dt) AS prev_amount  -- Get the previous day's amount
  FROM  daily_sales
)
SELECT
  dt,
  amount,
  prev_amount,
  ROUND(
    (amount - prev_amount)
      / NULLIF(prev_amount, 0) * 100.0,  -- Percentage change (division-by-zero guard)
    1
  ) AS pct_change,
  CASE
    WHEN (amount - prev_amount) / NULLIF(prev_amount, 0) >= 0.5   -- +50% or more
      THEN 'spike'
    WHEN (amount - prev_amount) / NULLIF(prev_amount, 0) <= -0.3  -- -30% or less
      THEN 'drop'
    ELSE 'normal'
  END AS alert_type
FROM  with_prev
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. Define CTE with_prev   → Evaluate the window function (preserve row count)
  2. Evaluate outer query   → Evaluate columns (pct_change, flag)
  3. ORDER BY dt            → Sort and return the output
*/