Anomaly Detection — Learn MAD, EWMA, and Consecutive Anomaly Detection in Practice

QUESTION 1

Segmented Moving Statistics — Build independent baselines per store with PARTITION BY

PARTITION BYROWS BETWEENSegmented AggregationMoving Statistics

Background ▼

In the fundamentals section we worked with a single series, but real-world data usually mixes multiple segments—stores, products, or users—in one table. If you calculate a moving average across segments, the first row of one store can slip into the frame after the last row of another store, producing a meaningless baseline. PARTITION BY prevents this.

AVG(amount) OVER (
  PARTITION BY store_id   -- Create a wall for each segment
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- The frame resets independently for each PARTITION BY group
-- Moving averages and standard deviations recalculate at a store boundary

PARTITION BY is a “wall” that splits the window: A window function proceeds as split into groups with PARTITION BY → ORDER BY within each partition → determine the frame with ROWS BETWEEN. A frame never crosses a partition boundary. Unlike GROUP BY, it does not reduce the row count; it attaches an aggregate value to every row.

Problem ▼

From the store_sales table of daily sales by store, calculate a three-day moving average (moving_avg) and moving standard deviation (moving_stddev) separately for each store (store_id). Return store_id, dt, amount, moving_avg, moving_stddev in ascending store_id, dt order. Round to two decimal places.

Table

► store_sales (8 rows / 2 stores × 4 days)

store_id	dt	amount
A	2024-01-01	100
A	2024-01-02	110
A	2024-01-03	105
A	2024-01-04	500
B	2024-01-01	50
B	2024-01-02	55
B	2024-01-03	52
B	2024-01-04	200

※ Store A and store B differ by about 2× in scale. Without PARTITION BY, A's values would contaminate B's baseline and both moving averages would be wrong. The final days (500 / 200) are spikes.

Expected Output ▼

Expected output (ascending store_id, dt):

store_id	dt	amount	moving_avg	moving_stddev
A	2024-01-01	100	100.00	NULL
A	2024-01-02	110	105.00	7.07
A	2024-01-03	105	105.00	5.00
A	2024-01-04	500	238.33	226.62
B	2024-01-01	50	50.00	NULL
B	2024-01-02	55	52.50	3.54
B	2024-01-03	52	52.33	2.52
B	2024-01-04	200	102.33	84.60

On the first day (01-01) of each store, the partition frame has only one row, so stddev is NULL. On 01-04, the spike causes stddev to expand sharply for both stores. Notice that no B value enters store A's baseline.

Model Answer ▼

SELECT
  store_id,
  dt,
  amount,
  ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
  ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev
FROM  store_sales
WINDOW w AS (
  PARTITION BY store_id          -- Keep each store's frame independent
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- Latest three days
)
ORDER BY store_id, dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM store_sales            → Read the rows
  2. WINDOW w (PARTITION/ORDER)  → Define the 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            → Sort and return the output
*/

Explanation (table transitions & key points) ▼

SELECT
  store_id,
  dt,
  amount,
  ROUND(AVG(amount)    OVER w, 2) AS moving_avg,
  ROUND(STDDEV(amount) OVER w, 2) AS moving_stddev
FROM  store_sales
WINDOW w AS (
  PARTITION BY store_id
  ORDER BY dt
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY store_id, dt;

LEGEND ▼

Rows read / loaded

① FROM store_sales (8 rows / 2 stores) ▼

FROM store_salesRead 8 rows for 2 stores (A and B) across 4 days. Store A has the larger sales scale and B the smaller one. The key issue is that these differently scaled series coexist in one table.

1 / 5

store_id	dt	amount
A	01-01	100
A	01-02	110
A	01-03	105
A	01-04	500
B	01-01	50
B	01-02	55
B	01-03	52
B	01-04	200

8 rows read

LEARNING POINTS

PARTITION BY is different from GROUP BY: GROUP BY collapses rows into aggregates, while PARTITION BY preserves the row count and attaches an aggregate value to each row. For anomaly detection, where every detail row needs its store's moving average, a window function with PARTITION BY is the right tool.

Composite partitions are possible: Specify multiple columns such as PARTITION BY store_id, product_id to create independent baselines at the store × product grain. In practice, combine store, product, channel, and weekday to define a precise normal range per segment.

Reuse a definition with the WINDOW clause: The moving average and moving standard deviation use the same frame, so WINDOW w AS (...) defines it once and OVER w shares it. This prevents repeated-frame mistakes and makes changes local.

ANTI-PATTERNS

Forget PARTITION BY and process every store as one series: This is the most common bug. With only OVER (ORDER BY dt), store B's first row follows store A's last row in the table, so A's values enter B's moving-average frame. Always add PARTITION BY when a table contains multiple segments.

Leave ties unresolved with ORDER BY dt alone: If one store has multiple rows for the same dt, the order of a ROWS BETWEEN frame is nondeterministic. Add a tie-breaker such as ORDER BY dt, id to make the order unique.

IN PRACTICE: Scaling segmented anomaly detection

E-commerce and SaaS systems monitor thousands or tens of thousands of segments (stores, products, and tenants) at once. Because normal ranges differ greatly by segment—a popular product and an unpopular product have very different “normal sales”—a single global threshold misses anomalies in smaller segments. Segment normalization with PARTITION BY is the most basic and important technique for preventing those misses. The z-score and EWMA techniques in later questions are also combined with PARTITION BY.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Leakage-Resistant z-Score — Exclude today with a trailing window (n PRECEDING AND 1 PRECEDING)

ROWS BETWEEN1 PRECEDINGLeakage Preventionz-score

Background ▼

Q3 in the fundamentals section had a weakness called “spike contamination.” If the mean and standard deviation include today, the spike itself pushes up those statistics and makes its own z-score smaller. The practical standard is to build the baseline from a trailing window containing only the past. Then the spike day is compared with an uncontaminated history and can be detected correctly.

AVG(amount) OVER (
  ORDER BY dt
  ROWS BETWEEN 5 PRECEDING
       AND 1 PRECEDING   -- Exclude today (CURRENT ROW)!
)
-- Frame = one to five days ago, at most five rows (today excluded)
-- Today's value does not affect the baseline at all = no leakage

Change the frame end from CURRENT ROW to 1 PRECEDING: The fundamentals version included today with ... AND CURRENT ROW. Changing only the end to 1 PRECEDING creates a pure historical baseline that excludes today. This is the same idea as preventing data leakage in machine learning: never build the detection baseline from the value being tested.

Problem ▼

From the daily_sales table, calculate a z-score using the moving average (base_avg) and moving standard deviation (base_stddev) of the previous five days only (one to five days ago), then set anomaly_flag to 'anomaly' when |z| ≥ 3 and 'normal' otherwise. Return dt, amount, base_avg, base_stddev, z_score, anomaly_flag in ascending dt order. Round base_avg, base_stddev, and z_score to two decimal places.

Table

► daily_sales (8 rows)

dt	amount
2024-01-01	100
2024-01-02	101
2024-01-03	100
2024-01-04	101
2024-01-05	100
2024-01-06	300
2024-01-07	101
2024-01-08	100

※ The 300 on 01-06 is an obvious spike. With the fundamentals window (including today), the spike inflates stddev and is missed at about z≈1.7; with today's value excluded, z becomes much larger and it is detected correctly.

Expected Output ▼

Expected output (ascending dt):

dt	amount	base_avg	base_stddev	z_score	anomaly_flag
2024-01-01	100	NULL	NULL	NULL	normal
2024-01-02	101	100.00	NULL	NULL	normal
2024-01-03	100	100.50	0.71	-0.71	normal
2024-01-04	101	100.33	0.58	1.15	normal
2024-01-05	100	100.50	0.58	-0.87	normal
2024-01-06	300	100.40	0.55	364.42	anomaly
2024-01-07	101	140.40	89.22	-0.44	normal
2024-01-08	100	140.40	89.22	-0.45	normal

The 01-06 spike is compared with uncontaminated history (avg=100.4, std=0.55), so z=364 is extreme and is detected with certainty. On 01-07 and 01-08, the spike enters the historical window and inflates stddev, temporarily reducing sensitivity (see the column). The first two rows have insufficient frame elements and therefore NULL.

Model Answer ▼

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
*/

Explanation (table transitions & key points) ▼

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
  )
)
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),
    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;

LEGEND ▼

Rows read / loaded

① FROM daily_sales (8 rows) ▼

FROM daily_salesRead 8 rows. The series is stable through 01-05, then a 300 spike appears on 01-06. Recall that the fundamentals question missed this spike.

1 / 6

dt	amount
01-01	100
01-02	101
01-03	100
01-04	101
01-05	100
01-06	300
01-07	101
01-08	100

8 rows read

LEARNING POINTS

“Do not build the baseline from the value being detected” prevents leakage: Like a machine-learning forecast, anomaly detection must not use today's observation in today's baseline. Including it lets a spike raise its own threshold and hide itself. Excluding today with ... AND 1 PRECEDING removes this self-contamination.

Frame-boundary vocabulary: Combine n PRECEDING (n rows earlier), 1 PRECEDING (one row earlier), CURRENT ROW (today), n FOLLOWING (n rows later), and UNBOUNDED PRECEDING (from the beginning) to create arbitrary windows. For “past only,” end at 1 PRECEDING; for “future only,” start at 1 FOLLOWING.

Guard the minimum number of elements: Near the beginning, the frame is too small and stddev becomes NULL. In production, combine it with COUNT(*) OVER w >= 3 so that only rows with enough baseline data are classified, suppressing unstable early detections.

ANTI-PATTERNS

Wonder why detection fails when the window includes today: With ROWS BETWEEN 5 PRECEDING AND CURRENT ROW, the spike inflates stddev and makes z smaller. This is a typical source of missed detections. Include today for smoothing or visualization, but exclude it from an anomaly-classification baseline.

Miss the sensitivity drop after a spike: Even with today excluded, the spike enters the historical window on 01-07 and 01-08, inflating stddev and reducing sensitivity. A window like this can miss repeated spikes or step changes, so combine it with Q3's robust MAD statistics or Q5's consecutive-anomaly detection.

IN PRACTICE: Training windows and real-time detection

Production time-series anomaly detectors—such as Prophet, Twitter AnomalyDetection, and various APM tools—predict and evaluate today using only past observations. A SQL trailing window is the smallest implementation of that idea. The one-word difference between including and excluding today can determine whether an anomaly is detected, so production query reviews always check it. To avoid the sensitivity loss caused by spikes entering the window, advanced practitioners often use median and MAD (the next question) instead of mean and standard deviation.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Robust Anomaly Detection — Build an outlier-resistant score with the median and MAD

PERCENTILE_CONTMADRobust StatisticsModified z-score

Background ▼

z-scores have a weakness: the mean and standard deviation themselves are pulled toward outliers. When one large spike appears, both statistics inflate and the spike's own z-score becomes smaller, causing it to be missed. This masking effect is addressed with the outlier-resistant median and MAD (median absolute deviation).

-- MAD = median( |x_i - median(x)| )
-- Modified z-score (Iglewicz-Hoaglin):
modified_z = 0.6745 * (x - median) / MAD
-- 0.6745 = the 0.75 quantile of the standard normal distribution
-- |modified_z| > 3.5 is a common outlier guideline

Why the median and MAD resist outliers: The median is the 50th-percentile value, so it is unaffected when fewer than half the data points are outliers (50% breakdown point). The mean has a 0% breakdown point and can fail from a single point. MAD has the same robustness. This is robust statistics: a stable baseline even in contaminated data.

Problem ▼

From the daily_sales table, calculate the all-period median (median_val) and MAD (mad_val), then each day's modified z-score (mod_z = 0.6745×(amount−median)/MAD). Set flag to 'anomaly' when |mod_z| > 3.5 and 'normal' otherwise. Return dt, amount, median_val, mad_val, mod_z, flag in ascending dt order. Round mod_z to two decimal places.

Table

► daily_sales (8 rows)

dt	amount
2024-01-01	100
2024-01-02	102
2024-01-03	98
2024-01-04	101
2024-01-05	99
2024-01-06	500
2024-01-07	103
2024-01-08	100

※ The 500 on 01-06 is an extreme spike. With an ordinary z-score, the mean expands to 150 and the spike's own z≈2.47 stays below |z|≥3, so it is missed (masking). With MAD, mod_z≈179.6 and it is detected with certainty. That is the point of this question.

Expected Output ▼

Expected output (ascending dt):

dt	amount	median_val	mad_val	mod_z	flag
2024-01-01	100	100.50	1.50	-0.22	normal
2024-01-02	102	100.50	1.50	0.67	normal
2024-01-03	98	100.50	1.50	-1.12	normal
2024-01-04	101	100.50	1.50	0.22	normal
2024-01-05	99	100.50	1.50	-0.67	normal
2024-01-06	500	100.50	1.50	179.64	anomaly
2024-01-07	103	100.50	1.50	1.12	normal
2024-01-08	100	100.50	1.50	-0.22	normal

median=100.5 and MAD=1.5 are almost unaffected by the 500 spike (the same all-period values are used for every row). Only 01-06 has mod_z=179.6 and becomes 'anomaly'. Because the spike does not contaminate median/MAD, normal points retain small mod_z values.

Model Answer ▼

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),  -- 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
*/

Explanation (table transitions & key points) ▼

WITH med AS (
  SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_val
  FROM  daily_sales
),
dev AS (
  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
),
mad AS (
  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),
    2
  ) AS mod_z,
  CASE WHEN ABS(... ) > 3.5 THEN 'anomaly' ELSE 'normal' END AS flag
FROM  dev d
CROSS JOIN mad m
ORDER BY d.dt;

LEGEND ▼

Rows read / loaded

① Sort ascending — Prepare the median ▼

WITHIN GROUP (ORDER BY amount)Sort all 8 rows by amount. The median is the midpoint between the fourth and fifth values. The 500 spike is pushed to the end as the maximum and does not affect the central value.

1 / 6

Rank	dt	amount (ascending)
1	01-03	98
2	01-05	99
3	01-01	100
4	01-08	100
5	01-04	101
6	01-02	102
7	01-07	103
8	01-06	500

8 rows (sorted ascending)

LEARNING POINTS

Masking is a critical weakness of z-scores: With this data, an ordinary z-score has a mean of 150.4 and a standard deviation of about 141, so the 500 spike's own z is only ≈2.47. It stays below the |z|≥3 threshold and is missed. The median and MAD are rank-based and insensitive to the outlier's magnitude, avoiding this trap.

Meaning of 0.6745: MAD is smaller than standard deviation σ by itself. For a normal distribution, MAD ≈ 0.6745σ; multiplying deviation/MAD by 0.6745 puts the score on the same σ-unit scale as a conventional z-score, making threshold 3.5 comparable.

Three CTEs express “two levels of medians”: MAD takes the median twice: first the median of the values, then the median of absolute deviations. The med → dev → mad CTE chain and CROSS JOIN pattern distribute a one-row aggregate result to every row.

ANTI-PATTERNS

Divide by zero when MAD=0: If more than half the data has the same value, MAD becomes 0 and mod_z diverges. Guard with NULLIF(mad_val, 0), and if needed provide a fallback such as mean absolute deviation or a small floor value.

Replace the median of abs_dev with an average: Using AVG(abs_dev) produces mean absolute deviation (MeanAD) and loses MAD's robustness because the mean is sensitive to outliers. Always calculate MAD with the median, PERCENTILE_CONT(0.5).

IN PRACTICE: When to use MAD

A MAD-based modified z-score is more reliable than a z-score when multiple outliers may occur, the distribution is skewed, or the dataset is small. It is common in contaminated environments such as payment-fraud detection, faulty IoT sensor readings, and abnormal log spikes. A window version that calculates median/MAD for every frame—PERCENTILE_CONT(...) OVER (...)—is expensive, so production often uses all-period batch aggregation (this question) or a segment-level GROUP BY. Q4 follows trends with EWMA, while Q5 detects persistence.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

EWMA (Exponentially Weighted Moving Average) — Smooth a series by recursively updating the previous value

WITH RECURSIVEUNION ALLEWMARecursive Smoothing

Background ▼

A simple moving average (SMA) abruptly “forgets” old values when they leave the window, which can make the smoothed curve jagged. An EWMA (exponentially weighted moving average) gives more weight to recent values and exponentially less weight to older values, following trends smoothly but quickly. Because each EWMA value is updated from the previous EWMA, a recursive CTE (WITH RECURSIVE) is a natural implementation.

-- EWMA recurrence (α = smoothing factor, 0<α≤1)
ema₁ = amount₁                       -- First term (anchor)
ema_t = α·amount_t + (1−α)·ema_t−1   -- Recurrence
-- Larger α → emphasize recent values (faster response)
-- Smaller α → emphasize history (smoother)

-- ▼ Basic WITH RECURSIVE syntax ▼
WITH RECURSIVE cte_name AS (
  -- ① Non-recursive term (anchor: the first row)
  SELECT 1 AS rn, initial_value AS val

  UNION ALL

  -- ② Recursive term (reference cte_name to create the next row)
  SELECT c.rn + 1, calculate_next_val(c.val)
  FROM cte_name c
  WHERE c.rn < limit_condition
)
SELECT * FROM cte_name;

WITH RECURSIVE has two parts: A recursive CTE connects a non-recursive term (anchor: the first row) and a recursive term (reference itself to create the next row) with UNION ALL. JOIN seq ON s.rn = e.rn + 1 carries forward the previous row's ema and repeats until no next rn exists.

Problem ▼

From the daily_sales table, calculate an EWMA (ewma) with smoothing factor α=0.5 using a recursive CTE. Return dt, amount, ewma in ascending dt order. Round ewma to two decimal places. The first row's ewma equals amount.

Table

► daily_sales (6 rows)

dt	amount
2024-01-01	100
2024-01-02	100
2024-01-03	100
2024-01-04	200
2024-01-05	100
2024-01-06	100

※ The 200 on 01-04 is a spike. EWMA responds quickly to 150 that day, then decays exponentially to 125→112.5. This combination of responsiveness and smooth decay is the difference from SMA.

Expected Output ▼

Expected output (ascending dt):

dt	amount	ewma
2024-01-01	100	100.00
2024-01-02	100	100.00
2024-01-03	100	100.00
2024-01-04	200	150.00
2024-01-05	100	125.00
2024-01-06	100	112.50

ema4 = 0.5×200 + 0.5×100 = 150. ema5 = 0.5×100 + 0.5×150 = 125. ema6 = 0.5×100 + 0.5×125 = 112.5. The spike's influence trails off by half at each step, which is characteristic of EWMA.

Model Answer ▼

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
*/

Explanation (table transitions & key points) ▼

WITH RECURSIVE seq AS (
  SELECT dt, amount,
    ROW_NUMBER() OVER (ORDER BY dt) AS rn
  FROM daily_sales
),
ewma AS (
  SELECT rn, dt, amount, amount::numeric AS ema
  FROM seq
  WHERE rn = 1
  UNION ALL
  SELECT s.rn, s.dt, s.amount,
    0.5 * s.amount + 0.5 * e.ema
  FROM ewma e
  JOIN seq s ON s.rn = e.rn + 1
)
SELECT
  dt, amount,
  ROUND(ema, 2) AS ewma
FROM  ewma
ORDER BY rn;

LEGEND ▼

Rows read / loaded

① CTE seq — Assign rn with ROW_NUMBER() ▼

ROW_NUMBER() OVER (ORDER BY dt) AS rnAssign rn=1..6 to establish the traversal order for recursion. The recursive term follows this sequence one row at a time with s.rn = e.rn + 1.

1 / 7

rn	dt	amount
1	01-01	100
2	01-02	100
3	01-03	100
4	01-04	200
5	01-05	100
6	01-06	100

6 rows (rn assigned)

LEARNING POINTS

The heart of a recursive CTE is carrying forward the previous result: The recursive term's FROM ewma e JOIN seq s ON s.rn = e.rn + 1 generates exactly one next row from the last accumulated EWMA row (e). One row is added per iteration; when the next rn is absent, the JOIN is empty and recursion stops naturally.

EWMA vs. SMA: SMA averages the values in a window equally and abruptly forgets a value after it leaves. EWMA remembers all history with exponential decay, so it reacts quickly to a spike (immediately to 150) and decays smoothly afterward (125→112.5). One α controls the response speed.

Fix the type with ::numeric: The anchor's amount::numeric makes the decimal calculation in 0.5 * ... type-compatible. A recursive CTE requires matching column types between the anchor and recursive terms, so align the type explicitly in the initial term.

ANTI-PATTERNS

Join directly on dt instead of using rn: With missing or duplicate dates, an e.dt + interval '1 day' approach can stop early or multiply rows. Use ROW_NUMBER() to follow the unique “next row” sequence.

Break the termination condition and recurse forever: If the recursive JOIN condition is changed from s.rn = e.rn + 1 to an inequality or an accidental self-join, rows keep multiplying and PostgreSQL eventually errors at max_recursion. Enforce a design that advances exactly one row per iteration.

IN PRACTICE: EWMA and production monitoring tools

EWMA is widely used in anomaly detection for monitoring tools such as Datadog, Prometheus, and CloudWatch. It is memory-efficient—each update needs only the latest point and the previous value—and suits streaming. A recursive CTE is excellent for making the idea visible, but for large datasets it is more practical to approximate with window functions or calculate incrementally in an application or dbt incremental model. Turning the residual (amount − ewma) into a z-score creates trend-following anomaly detection (an EWMA control chart), a standard quality-control method.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Detect Consecutive Anomalies — Use gaps & islands to alert only on persistent anomalies, not one-off noise

ROW_NUMBERgaps & islandsConsecutive GroupingNoise Suppression

Background ▼

A one-day threshold breach is often measurement noise or a temporary event. Alerting on every isolated spike causes alert fatigue. In practice, the standard is to notify only when an anomaly continues for N consecutive days. The classic gaps & islands technique (grouping consecutive runs) implements this rule.

-- Identify consecutive groups with the difference of two row numbers
grp = ROW_NUMBER() OVER (ORDER BY dt)
    - ROW_NUMBER() OVER (PARTITION BY is_high ORDER BY dt)
-- Consecutive rows with the same is_high have a constant grp
-- When continuity breaks, grp changes and a new island begins

Why the difference of two row numbers reveals continuity: The overall row number increases by one, and the row number within is_high also increases by one while the condition remains consecutive. Their difference stays constant during a run and changes when the run breaks. Use that difference as the island ID (grp), then measure each island with COUNT(*) OVER (PARTITION BY is_high, grp).

Problem ▼

In the daily_sales table, treat amount ≥ 200 as high level (is_high=1). Set alert_type to 'sustained_anomaly' only for high-level runs lasting at least three days, 'noise' for isolated or two-day high-level runs, and 'normal' otherwise. Return dt, amount, is_high, run_length, alert_type in ascending dt order.

Table

► daily_sales (13 rows)

dt	amount
2024-01-01	100
2024-01-02	300
2024-01-03	100
2024-01-04	100
2024-01-05	300
2024-01-06	320
2024-01-07	310
2024-01-08	100
2024-01-09	300
2024-01-10	100
2024-01-11	310
2024-01-12	300
2024-01-13	100

※ 01-02 and 01-09 are isolated high-level days (one day) and therefore noise. 01-11–12 is a two-day noise run. 01-05–07 is a three-day high-level run and therefore a sustained anomaly. The goal is to distinguish short events from persistence.

Expected Output ▼

Expected output (ascending dt):

dt	amount	is_high	run_length	alert_type
2024-01-01	100	0	1	normal
2024-01-02	300	1	1	noise
2024-01-03	100	0	2	normal
2024-01-04	100	0	2	normal
2024-01-05	300	1	3	sustained_anomaly
2024-01-06	320	1	3	sustained_anomaly
2024-01-07	310	1	3	sustained_anomaly
2024-01-08	100	0	2	normal
2024-01-09	300	1	1	noise
2024-01-10	100	0	2	normal
2024-01-11	310	1	2	noise
2024-01-12	300	1	2	noise
2024-01-13	100	0	1	normal

01-02, 01-09, and 01-11–12 have run_length below 3 and are excluded as short-term noise. 01-05–07 has run_length=3 and becomes 'sustained_anomaly'. The run_length of normal runs is informational and does not affect the alert.

Model Answer ▼

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
*/

Explanation (table transitions & key points) ▼

WITH flagged AS (
    SELECT dt, amount,
    CASE WHEN amount >= 200 THEN 1 ELSE 0 END AS is_high
  FROM  daily_sales
),
rownums AS (
    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 (
    SELECT dt, amount, is_high,
    (rn_all - rn_flag) AS grp
  FROM rownums
),
runs AS (
    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;

LEGEND ▼

Rows read / loaded

① FROM daily_sales (13 rows) ▼

FROM daily_salesRead 13 days of data. Spikes appear as isolated days (01-02, 01-09), three consecutive days (01-05–07), and two consecutive days (01-11–12).

1 / 7

dt	amount
01-01	100
01-02	300
01-03	100
01-04	100
01-05	300
01-06	320
01-07	310
01-08	100
01-09	300
01-10	100
01-11	310
01-12	300
01-13	100

13 rows read

LEARNING POINTS

The difference of row numbers is the key to gaps & islands: The difference between the overall sequence and the condition-specific sequence is constant only while rows are consecutive. This uses the fact that subtracting one arithmetic sequence from another yields a constant over a run, making it a general SQL idiom for consecutive groups. It applies to consecutive login days, out-of-stock periods, and repeated errors.

The island key is the pair (is_high, grp): If grp alone is used, an is_high=0 island and an is_high=1 island can happen to share a value. Use PARTITION BY is_high, grp and the two-column pair uniquely identifies an island, so only high-level islands are counted correctly.

Classify persistence, not just frequency: Simply counting anomalies lets scattered noise accumulate into an alert. Requiring consecutive days (persistence) separates a transient event from a real incident that continues. Tune the run_length threshold (three days here) to the domain.

ANTI-PATTERNS

Stack many LAG calls for consecutive detection: Listing LAG(is_high,1), LAG(is_high,2), … to test “three consecutive days” must be rewritten whenever the run length changes and does not scale. Build islands with gaps & islands and measure their lengths with COUNT.

Use ROW_NUMBER sequences when dates have gaps: This question assumes one row for every day. If dates are missing, the row-number difference measures consecutive rows, not consecutive calendar days. For strict calendar continuity, generate a date grid, fill it with LEFT JOIN, and then apply the grouping.

IN PRACTICE: Alert design and the anomaly-detection recap

“Fire after N consecutive days” is standard in monitoring through Prometheus Alertmanager's “for” clause and Datadog's “trigger after N occurrences.” Writing the same logic in SQL lets the data platform design pre-alert aggregation and suppression (dedup / debounce). As a recap, anomaly detection is layered rather than a single method: ① segment normalization (PARTITION BY) → ② leakage-resistant baseline (historical window) → ③ robust statistics (MAD) → ④ trend following (EWMA) → ⑤ persistence suppression (gaps & islands). Understand what each technique handles well and where it is weak, then combine them for the domain.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.