Anomaly Detection — Learn Rolling z-Scores, Seasonality, and Composite Signals in Practice

QUESTION 6

Rolling z-Score — Measure anomalies against a recent dynamic baseline while preventing leakage and warm-up false positives

ROWS BETWEEN n PRECEDINGExclude current row / leakageRolling z-ScoreDynamic-baseline anomaly detection

Background ▼

Q3 used a fixed mean and standard deviation over the entire period as the z-score baseline. But when a series has a trend or a level shift, a fixed baseline quickly becomes stale. In practice, a moving z-score (rolling z) based on only the "most recent N days" is standard. The two keys are to exclude the current day from the baseline (do not evaluate a value against itself—avoid leakage) and to defer classification until enough baseline data has accumulated (min_periods).

AVG(amount)    OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
-- "3 PRECEDING AND 1 PRECEDING" = use only the prior three days, excluding today
-- With "... AND CURRENT ROW", today (the value being evaluated) would enter the baseline

Excluding today is the key to avoiding leakage: Ending the frame at 1 PRECEDING instead of CURRENT ROW keeps today's value out of the baseline calculation. This prevents an anomalous value from inflating its own baseline mean and standard deviation and making itself look normal (an application of Q1's frame specification).

Problem ▼

From sensor_readings, calculate the moving average roll_avg and moving standard deviation roll_std over the prior three days (excluding today), then calculate z =(amount − roll_avg)/roll_std. Rows with fewer than three usable baseline days, n_base<3, must be held as 'warmup'; otherwise, classify |z|≥2 as 'anomaly' and smaller values as 'normal'. Return dt, amount, roll_avg, roll_std, z_score, status in ascending dt order. Round the average, standard deviation, and z-score to two decimal places.

Table

► sensor_readings (9 rows)

dt	amount
2024-01-01	100
2024-01-02	96
2024-01-03	104
2024-01-04	100
2024-01-05	96
2024-01-06	200
2024-01-07	100
2024-01-08	96
2024-01-09	104

※ The normal pattern cycles through 96/100/104. 01-06 (200) is the only spike. Its prior three days are always {96,100,104}, giving a stable mean of 100 and SD of 4, so 200 produces the obvious z=25.

Expected Output ▼

Expected output (ascending dt):

dt	amount	roll_avg	roll_std	z_score	status
2024-01-01	100	NULL	NULL	NULL	warmup
2024-01-02	96	100.00	NULL	NULL	warmup
2024-01-03	104	98.00	2.83	2.12	warmup
2024-01-04	100	100.00	4.00	0.00	normal
2024-01-05	96	100.00	4.00	-1.00	normal
2024-01-06	200	100.00	4.00	25.00	anomaly
2024-01-07	100	132.00	58.92	-0.54	normal
2024-01-08	96	132.00	58.92	-0.61	normal
2024-01-09	104	132.00	58.92	-0.48	normal

01-03 has |z|=2.12, but its baseline contains only two days (n_base<3), so it is held as warmup to suppress a false positive. From 01-06 onward, note that the baseline SD has expanded to 58.92 because the spike contaminates later baselines.

Model Answer ▼

WITH rolling AS (
  SELECT
    dt, amount,
    AVG(amount)    OVER w AS roll_avg,   -- Moving average over the prior three days
    STDDEV(amount) OVER w AS roll_std,   -- Moving SD over the prior three days (=STDDEV_SAMP)
    COUNT(*)       OVER w AS n_base     -- Number of prior days used as the baseline
  FROM sensor_readings
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING    -- Prior three days, excluding today
  )
)
SELECT
  dt, amount,
  ROUND(roll_avg, 2) AS roll_avg,
  ROUND(roll_std, 2) AS roll_std,
  ROUND((amount - roll_avg) / NULLIF(roll_std, 0), 2) AS z_score,
  CASE
    WHEN n_base < 3 THEN 'warmup'                          -- Hold rows with insufficient baseline data
    WHEN ABS((amount - roll_avg) / NULLIF(roll_std, 0)) >= 2
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM rolling
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM sensor_readings   → Read the rows
  2. Define WINDOW w         → Define the prior-three-day frame
  3. AVG/STDDEV/COUNT OVER   → Evaluate window functions (row count preserved)
  4. SELECT                  → Evaluate columns (rolling z-score)
  5. CASE                    → Classify warmup / anomaly
  6. ORDER BY dt             → Sort and return the output
*/

Explanation (table transitions & key points) ▼

WITH rolling AS (
  SELECT
    dt, amount,
    AVG(amount) OVER w AS roll_avg,
    STDDEV(amount) OVER w AS roll_std,
    COUNT(*) OVER w AS n_base
  FROM sensor_readings
  WINDOW w AS (
    ORDER BY dt
    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
  )
)
SELECT
  dt, amount,
  ROUND(roll_avg, 2) AS roll_avg,
  ROUND(roll_std, 2) AS roll_std,
  ROUND((amount - roll_avg) / NULLIF(roll_std, 0), 2) AS z_score,
  CASE
    WHEN n_base < 3 THEN 'warmup'
    WHEN ABS((amount - roll_avg) / NULLIF(roll_std, 0)) >= 2
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM rolling
ORDER BY dt;

LEGEND ▼

Rows read / loaded

① FROM sensor_readings (9 rows) ▼

FROM sensor_readingsThe series normally cycles through 96/100/104, with only 01-06 (200) spiking. Instead of a fixed full-period baseline, use the prior three days as a moving baseline to detect observations outside the local normal range.

1 / 6

dt	amount
01-01	100
01-02	96
01-03	104
01-04	100
01-05	96
01-06	200
01-07	100
01-08	96
01-09	104

9 rows read

LEARNING POINTS

Fixed baseline vs. moving baseline: Q3's AVG(amount) OVER () used a fixed baseline over the full period, so it could not follow trends or level shifts. OVER (ORDER BY dt ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) creates a local recent baseline that absorbs gradual change as normal while catching sudden deviations.

End at 1 PRECEDING to exclude today and avoid leakage: With CURRENT ROW as the frame end, today's value enters its own baseline and an anomaly can inflate its own mean and SD and normalize itself. Excluding today with 1 PRECEDING is the rule. This is a direct application of Q1's frame specification.

Use min_periods to suppress startup false positives: When only one or two baseline days exist, SD is unstable and even small changes can make z swing wildly. Count baseline days with COUNT(*) OVER w and hold n_base < 3 as warmup to prevent undersampled false positives such as 01-03 (z=2.12).

ANTI-PATTERNS

Include today in the baseline: With ROWS BETWEEN 3 PRECEDING AND CURRENT ROW, a spike raises its own baseline mean and SD and makes its z-score artificially small. Always keep the value being evaluated out of its baseline.

Classify the startup period unconditionally: If the early period is not held as warmup while the baseline is still filling, a rolling z-score produces many false alerts at the beginning. Set a minimum baseline count first. Conversely, immediately after a spike, the baseline SD can expand (58.92 in this question), making later anomalies easier to miss.

IN PRACTICE: Rolling z-scores and self-contamination

Moving baselines are powerful, but they have a weakness: a detected anomaly can be absorbed into later baselines and distort them. Here, 200 on 01-06 expands the later SD to 58.92. Countermeasures include excluding values already classified as anomalous from baseline calculations, or using outlier-resistant statistics such as the median and MAD (median absolute deviation). Establishing the foundation of "exclude today + min_periods" is the first practical step.

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 7

Detect Consecutive Anomalies — Use Gaps & Islands to group only runs of N consecutive anomalous days

ROW_NUMBER difference trickDate − row numberGaps & IslandsPersistent-incident detection

Background ▼

A one-off anomaly (point anomaly) means something different from a persistent incident where the anomaly continues for several days. The latter is the issue that usually deserves a response. Gaps & Islands is the classic technique for grouping consecutive rows into one island. Its core property is that the difference from a sequence number stays constant only while rows are consecutive.

-- Sort anomalous days by date and assign a row number...
dt:   01-05  01-06  01-07     01-10  01-11
rn:     1      2      3         4      5
dt-rn: 01-04  01-04  01-04     01-06  01-06   ← the value is constant within each consecutive run

Why "date − row number" becomes the island key: Consecutive dates increase by one day and the row number also increases by one, so their difference remains constant. When there is a gap, the difference shifts and a new island begins. Group by this difference to aggregate each consecutive run (combining Q6's ROW_NUMBER with Q9's GROUP BY/HAVING).

Problem ▼

From service_health (daily error rates), define error_rate > 5 as an anomalous day and extract only runs of at least three consecutive anomalous days (persistent incidents). For each run, calculate start_dt, end_dt, consecutive-day count run_days, and the maximum error rate peak_error. Return rows ordered by start_dt ascending.

Table

► service_health (12 rows)

dt	error_rate
2024-01-01	2
2024-01-02	8
2024-01-03	3
2024-01-04	2
2024-01-05	9
2024-01-06	7
2024-01-07	6
2024-01-08	1
2024-01-09	2
2024-01-10	10
2024-01-11	12
2024-01-12	3

※ Anomalous days (error_rate>5) are 01-02 / 01-05–07 / 01-10–11. There are three islands: one isolated day, one three-day run, and one two-day run. Filtering for "at least three days" leaves only 01-05–07.

Expected Output ▼

Expected output (ascending start_dt):

start_dt	end_dt	run_days	peak_error
2024-01-05	2024-01-07	3	9

The isolated 01-02 and the two-day run 01-10–11 are removed by HAVING COUNT(*) >= 3. Only the three-day run 01-05–07 remains as a persistent incident.

Model Answer ▼

WITH flagged AS (              -- ① Keep anomalous days only
  SELECT dt, error_rate
  FROM service_health
  WHERE error_rate > 5
),
grouped AS (                       -- ② Create an island key with date − row number
  SELECT
    dt, error_rate,
    dt - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY dt) AS grp
  FROM flagged
)
SELECT                            -- ③ Aggregate islands and keep runs of at least three days
  MIN(dt)         AS start_dt,
  MAX(dt)         AS end_dt,
  COUNT(*)        AS run_days,
  MAX(error_rate) AS peak_error
FROM grouped
GROUP BY grp
HAVING COUNT(*) >= 3             -- Keep only islands lasting at least three days
ORDER BY start_dt;

/*
  Execution order (logical SQL evaluation order):
  1. WHERE error_rate > 5    → Filter rows
  2. ROW_NUMBER()             → Evaluate the window function (row count preserved)
  3. GROUP BY grp             → Group the consecutive runs
  4. MIN/MAX/COUNT             → Evaluate aggregate functions
  5. HAVING COUNT(*) >= 3      → Filter groups
  6. ORDER BY start_dt         → Sort and return the output
*/

Explanation (table transitions & key points) ▼

WITH flagged AS (
  SELECT dt, error_rate
  FROM service_health
  WHERE error_rate > 5
),
grouped AS (
  SELECT
    dt, error_rate,
    dt - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY dt) AS grp
  FROM flagged
)
SELECT
  MIN(dt) AS start_dt,
  MAX(dt) AS end_dt,
  COUNT(*) AS run_days,
  MAX(error_rate) AS peak_error
FROM grouped
GROUP BY grp
HAVING COUNT(*) >= 3
ORDER BY start_dt;

LEGEND ▼

Rows read / loaded

① FROM service_health (12 rows) — Anomalous days (>5) highlighted ▼

FROM service_healthDaily error rates. Applying error_rate>5 separates anomalous and normal days, producing isolated, three-day, and two-day anomalous runs. Looking at rows individually does not reveal continuity.

1 / 7

dt	error_rate
01-01	2
01-02	8
01-03	3
01-04	2
01-05	9
01-06	7
01-07	6
01-08	1
01-09	2
01-10	10
01-11	12
01-12	3

12 rows read (highlight = anomaly >5)

LEARNING POINTS

Date − row number becomes the island key: Consecutive dates advance one day at a time and the row number advances one at a time, so their difference is invariant within a run. When a date is missing, the difference jumps and a new island starts. This is the heart of Gaps & Islands.

Distinguish point anomalies from persistent anomalies: A one-off spike and an N-day deviation have different urgency. Group consecutive runs and apply HAVING COUNT(*) >= N to remove noisy one-offs and extract real incidents only (an application of Q9's HAVING).

An integer sequence works independently of date types: This question uses dt - INTERVAL '1 day' * rn, but if a gap-free integer sequence exists, day_no - rn creates the same island key. When missing dates are allowed by the definition, integer subtraction can be more portable.

ANTI-PATTERNS

Use LAG to check only whether the previous row is consecutive: Checking whether the previous day was anomalous with LAG does not calculate how long the run lasted. If you need run length and start/end dates, create an island key and GROUP BY it.

Assign row numbers before filtering: If ROW_NUMBER is assigned while normal days are still present, the sequence advances through normal days and the island key breaks. Always filter to anomalous days with WHERE before assigning row numbers.

IN PRACTICE: Gaps & Islands as SQL's Swiss Army knife

This technique appears anywhere consecutive periods matter: user login streaks, intervals when a sensor remains in one state, days of stockout, and uninterrupted contract periods. Once you understand the idea of representing continuity with the difference from a steadily increasing sequence, complicated period aggregation becomes surprisingly short SQL. In anomaly detection, it turns the intuition that "persistence means severity" directly into a query.

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 8

Seasonal Baseline — Use LAG(amount, 7) to compare the same weekday last week and avoid periodicity traps

LAG(col, n)Offset referenceWeekly seasonalitySeasonality-aware detection

Background ▼

Many metrics have weekly seasonality. Weekdays may be low while weekends are high, for example. Monitoring with a "previous-day comparison" turns normal weekly changes into false alerts every weekend and Monday. The fix is simple: compare with "the same weekday one week ago" instead of yesterday. LAG(amount, 7) retrieves the value seven rows earlier.

LAG(amount, 7) OVER (ORDER BY dt)   -- Seven days earlier (same weekday last week)
LAG(amount)    OVER (ORDER BY dt)   -- If omitted, the second argument defaults to 1 (yesterday)
-- Change the offset to switch the comparison baseline between yesterday and the same weekday last week

The second argument to LAG is the number of rows back: LAG(col, n) returns the value n rows before the current row (n defaults to 1). With daily data ordered by date, seven rows back is the same weekday last week. The first seven days have no comparison value and become NULL, so exclude them from classification as no_baseline.

Problem ▼

From daily_traffic (14 days with high weekend values and weekly seasonality), compare each day's amount with the value seven days earlier and calculate the week-over-week change rate wow_pct =(amount − base_7d)/base_7d×100. Assign 'no_baseline' to the first seven days without a seven-day baseline, 'anomaly' when |wow_pct| exceeds 50%, and 'normal' otherwise. Return dt, amount, base_7d, wow_pct, status in ascending dt order. Round pct to one decimal place.

Table

► daily_traffic (14 rows)

dt	amount
2024-01-01	100
2024-01-02	110
2024-01-03	120
2024-01-04	130
2024-01-05	140
2024-01-06	300
2024-01-07	310
2024-01-08	105
2024-01-09	115
2024-01-10	125
2024-01-11	135
2024-01-12	500
2024-01-13	305
2024-01-14	315

※ Strong weekly cycle: weekdays 100–140 on 01-01 (Mon)–05 (Fri), weekends around 300 on 01-06 (Sat)–07 (Sun). A previous-day comparison would falsely alert on every weekend (+114%) and Monday (−66%). 01-12 (Fri) is a true anomaly: 500 versus 140 on the same weekday last week (+257%).

Expected Output ▼

Expected output (ascending dt):

dt	amount	base_7d	wow_pct	status
2024-01-01	100	NULL	NULL	no_baseline
2024-01-02	110	NULL	NULL	no_baseline
2024-01-03	120	NULL	NULL	no_baseline
2024-01-04	130	NULL	NULL	no_baseline
2024-01-05	140	NULL	NULL	no_baseline
2024-01-06	300	NULL	NULL	no_baseline
2024-01-07	310	NULL	NULL	no_baseline
2024-01-08	105	100	5.0	normal
2024-01-09	115	110	4.5	normal
2024-01-10	125	120	4.2	normal
2024-01-11	135	130	3.8	normal
2024-01-12	500	140	257.1	anomaly
2024-01-13	305	300	1.7	normal
2024-01-14	315	310	1.6	normal

The weekend (01-13: 305 vs. 300 the previous week) is only +1.7% week over week and is normal. Aligning the seasonal cycle removes the false alerts and isolates the genuine 01-12 spike.

Model Answer ▼

WITH wow AS (
  SELECT
    dt, amount,
    LAG(amount, 7) OVER (ORDER BY dt) AS base_7d   -- Seven days earlier = same weekday last week
  FROM daily_traffic
)
SELECT
  dt, amount, base_7d,
  ROUND(100.0 * (amount - base_7d) / NULLIF(base_7d, 0), 1) AS wow_pct,
  CASE
    WHEN base_7d IS NULL THEN 'no_baseline'          -- First seven days have no comparison value
    WHEN ABS(100.0 * (amount - base_7d) / NULLIF(base_7d, 0)) > 50
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM wow
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM daily_traffic     → Read the rows
  2. LAG OVER (ORDER BY)     → Evaluate the window function (row count preserved)
  3. SELECT                  → Evaluate columns (wow_pct and anomaly status)
  4. ORDER BY dt             → Sort and return the output
*/

Explanation (table transitions & key points) ▼

WITH wow AS (
  SELECT
    dt, amount,
    LAG(amount, 7) OVER (ORDER BY dt) AS base_7d
  FROM daily_traffic
)
SELECT
  dt, amount, base_7d,
  ROUND(100.0 * (amount - base_7d) / NULLIF(base_7d, 0), 1) AS wow_pct,
  CASE
    WHEN base_7d IS NULL THEN 'no_baseline'
    WHEN ABS(100.0 * (amount - base_7d) / NULLIF(base_7d, 0)) > 50
      THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM wow
ORDER BY dt;

LEGEND ▼

Rows read / loaded

① FROM daily_traffic (14 rows) ▼

FROM daily_trafficFourteen days with a strong weekly cycle: weekday values around 100–140 and weekend values around 300. 01-12 (500) is the only true anomaly. Ignoring seasonality and comparing with the previous day would falsely alert on weekends and Mondays.

1 / 5

dt	amount
01-01	100
01-02	110
01-03	120
01-04	130
01-05	140
01-06	300
01-07	310
01-08	105
01-09	115
01-10	125
01-11	135
01-12	500
01-13	305
01-14	315

14 rows read

LEARNING POINTS

Align the comparison baseline with the cycle: A previous-day comparison on a seasonal series turns normal weekend and Monday periodic variation into anomalies. Comparing with the same weekday last week using LAG(amount, 7) cancels the weekday effect and leaves genuine anomalies. This extends Q5's previous-day comparison to seasonal data.

Use LAG's second argument to choose the offset: Change n in LAG(col, n) to change the comparison. For daily data, 7 means the previous week; for hourly data, 24 means the same hour yesterday; for monthly data, 12 means the same month last year. Choose n to match the data's cycle.

Explicitly exclude the period without a baseline: The first seven days have no LAG target and produce base_7d IS NULL. Classify them as no_baseline with CASE WHEN base_7d IS NULL instead of incorrectly calling them normal or anomalous (the same idea as Q6's warmup).

ANTI-PATTERNS

Monitor with a previous-day comparison while ignoring seasonality: For a series that rises on weekends and falls on Mondays, LAG(amount) would produce false alerts every weekend (+114%) and Monday (−66%) in this question. If a cycle exists, compare with an offset equal to its period.

Use LAG without specifying an order: LAG depends on the order in OVER (ORDER BY dt). Without ORDER BY, which row is "seven rows earlier" is undefined and the result breaks. Always specify ORDER BY for LAG and LEAD.

IN PRACTICE: Remove seasonality by subtraction or division

This question cancels seasonality with a week-over-week change rate, the simplest form of seasonal adjustment. More rigorously, subtract weekday averages for an additive model or divide by weekday factors for a multiplicative model, then apply Q6's rolling z-score to the adjusted residual. "Remove the cycle first, then detect anomalies in the remaining variation" is the standard way to handle seasonal real-world data.

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 9

Composite-Signal Severity — Combine level and sudden-change signals with CASE to classify severity

Multiple CASE branchesBoolean evaluation orderComposite signalsSeverity scoring

Background ▼

Operational alerts need more than a binary "anomaly or not" flag. A common approach is to combine multiple signals and rank events by severity. This question combines two independent perspectives—level (is the value high?) and jump (did it change sharply from the previous day?)—to produce four levels. The keys are the order of CASE branches and using boolean values directly as conditions.

CASE
  WHEN lvl_hi AND jump_hi THEN 'CRITICAL'  -- High level and sharp jump (classify first)
  WHEN lvl_hi             THEN 'WARNING'   -- High level but gradual
  WHEN jump_hi            THEN 'WATCH'     -- Sharp jump but low level
  ELSE 'OK'
END

CASE evaluates from top to bottom and stops at the first true branch: Put the strictest condition (CRITICAL) first. If the order is reversed, rows that qualify as CRITICAL are captured by WARNING first and their severity is understated.

Problem ▼

From metric_stream, calculate two signals: level signal lvl_hi =(amount > 200) and sudden-change signal jump_hi =(amount − previous amount ≥ 100). Combine them with CASE and assign severity as both true → 'CRITICAL' / level only → 'WARNING' / jump only → 'WATCH' / neither → 'OK'. Return dt, amount, delta, severity in ascending dt order, where delta = amount − previous amount.

Table

► metric_stream (8 rows)

dt	amount
2024-01-01	100
2024-01-02	105
2024-01-03	215
2024-01-04	220
2024-01-05	120
2024-01-06	235
2024-01-07	90
2024-01-08	195

※ Level means above 200; a jump means at least +100 from the previous day. 01-03 (215,+110) and 01-06 (235,+115) satisfy both and are CRITICAL. 01-04 (220,+5) is high but gradual → WARNING. 01-08 (195,+105) jumps while remaining below 200 → WATCH.

Expected Output ▼

Expected output (ascending dt):

dt	amount	delta	severity
2024-01-01	100	NULL	OK
2024-01-02	105	+5	OK
2024-01-03	215	+110	CRITICAL
2024-01-04	220	+5	WARNING
2024-01-05	120	-100	OK
2024-01-06	235	+115	CRITICAL
2024-01-07	90	-145	OK
2024-01-08	195	+105	WATCH

Among high-level rows, 01-03 and 01-06 are CRITICAL because they also jump, while gradual 01-04 is WARNING. 01-08 is below 200 but jumps sharply, so it is classified as WATCH as an early signal.

Model Answer ▼

WITH signals AS (
  SELECT
    dt, amount,
    amount - LAG(amount) OVER (ORDER BY dt) AS delta,           -- Difference from the previous day
    (amount > 200) AS lvl_hi,                                   -- Level signal (boolean)
    (amount - LAG(amount) OVER (ORDER BY dt) >= 100) AS jump_hi -- Sudden-change signal (boolean)
  FROM metric_stream
)
SELECT
  dt, amount, delta,
  CASE
    WHEN lvl_hi AND jump_hi THEN 'CRITICAL'   -- Test the strictest condition first
    WHEN lvl_hi             THEN 'WARNING'
    WHEN jump_hi            THEN 'WATCH'
    ELSE 'OK'
  END AS severity
FROM signals
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM metric_stream     → Read the rows
  2. LAG OVER (ORDER BY dt) → Evaluate the window function (row count preserved)
  3. SELECT                  → Evaluate columns (signals and CASE severity)
  4. ORDER BY dt             → Sort and return the output
*/

Explanation (table transitions & key points) ▼

WITH signals AS (
  SELECT
    dt, amount,
    amount - LAG(amount) OVER (ORDER BY dt) AS delta,
    (amount > 200) AS lvl_hi,
    (amount - LAG(amount) OVER (ORDER BY dt) >= 100) AS jump_hi
  FROM metric_stream
)
SELECT
  dt, amount, delta,
  CASE
    WHEN lvl_hi AND jump_hi THEN 'CRITICAL'
    WHEN lvl_hi             THEN 'WARNING'
    WHEN jump_hi            THEN 'WATCH'
    ELSE 'OK'
  END AS severity
FROM signals
ORDER BY dt;

LEGEND ▼

Rows read / loaded

① FROM metric_stream (8 rows) ▼

FROM metric_streamOne series of values. Evaluate it on two axes—whether the level is high and whether it jumped from the previous day—and combine them into four severity levels. This captures nuance that one threshold cannot express.

1 / 5

dt	amount
01-01	100
01-02	105
01-03	215
01-04	220
01-05	120
01-06	235
01-07	90
01-08	195

8 rows read

LEARNING POINTS

Put strict conditions first in CASE: CASE evaluates from top to bottom and stops at the first true branch. If CRITICAL (both conditions) is not first, a CRITICAL row can be captured as WARNING. Write the narrower, stricter condition before conditions that contain it.

Comparison expressions become boolean values: Expressions such as (amount > 200) AS lvl_hi can be retained as boolean columns. Combining them as WHEN lvl_hi AND jump_hi makes multi-signal logic readable. Naming intermediate results in a CTE clarifies the intent of the detection logic.

Composite signals reduce false alerts: With only amount>200, a gradually high row and a sudden spike are treated alike. Combining perspectives such as level × jump isolates high-priority "high and rising sharply" events and reduces alert fatigue.

ANTI-PATTERNS

Order CASE branches from loose to strict: If WHEN lvl_hi THEN 'WARNING' comes before CRITICAL, rows satisfying both conditions are finalized as WARNING and never reach CRITICAL. Consider coverage and exclusivity, and put strict conditions first.

Forget that boolean signals can contain NULL: On the first row, delta is NULL and jump_hi is also NULL. WHEN jump_hi does not treat NULL as true, so this question falls through to OK. If NULL should explicitly mean false, consider COALESCE(jump_hi, false). Always account for three-valued logic (true/false/NULL).

IN PRACTICE: Scoring and alert fatigue

This question uses two axes and four levels, but production systems often weight more signals—z-scores, drift, seasonal residuals, and volume ratios—into a continuous score, then cut it into CRITICAL/WARNING/INFO thresholds. The important principle is not to page on every signal at the same severity. As signals multiply, false positives multiply too, so triggering the top level only when multiple perspectives are active preserves operators' trust. Severity design matters as much as detection accuracy in the field.

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 10

Aggregate an Alert Summary — Use multiple FILTERs and GROUP BY ROLLUP to show categories and the grand total in one table

COUNT(*) FILTER × multipleGROUP BY ROLLUPSeverity-level aggregationSummary + totalCapstone

Background ▼

The advanced section closes by building an "alert summary" for operations. Extend Q10 from the fundamentals with COUNT(*) FILTER columns for multiple severity levels, count each severity at once, and use GROUP BY ROLLUP to place category detail rows and one grand-total row in the same result. One query produces a dashboard-ready table.

COUNT(*) FILTER (WHERE amount >= 300)                  -- Critical count
COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300)  -- Warning count
GROUP BY ROLLUP (category)   -- Category rows + one grand-total row (category=NULL)

ROLLUP creates detail plus total in one step: GROUP BY ROLLUP (category) adds one grand-total row to the ordinary category groups. That row has category=NULL, so label it with COALESCE(category,'(ALL)') and control ordering with GROUPING(category).

Problem ▼

From event_log, define amount>=300 as critical and 200≤amount<300 as warning. Aggregate total events, critical count, warning count, and critical rate % by category. Include a grand-total row for all categories with ROLLUP, label that row (ALL), and return category, total_events, critical, warning, crit_pct with category rows ascending and the grand total last. Round pct to one decimal place.

Table

► event_log (12 rows)

category	dt	amount
api	2024-01-01	350
api	2024-01-02	120
api	2024-01-03	130
api	2024-01-04	95
auth	2024-01-01	260
auth	2024-01-02	280
auth	2024-01-03	240
auth	2024-01-04	90
web	2024-01-01	100
web	2024-01-02	250
web	2024-01-03	300
web	2024-01-04	320

※ Red=critical(≥300); orange=warning(200–299). api: crit1/warn0, auth: crit0/warn3, web: crit2/warn1. Overall: crit3, warn4.

Expected Output ▼

Expected output (ascending category, grand total last):

category	total_events	critical	warning	crit_pct
api	4	1	0	25.0
auth	4	0	3	0.0
web	4	2	1	50.0
(ALL)	12	3	4	25.0

The final (ALL) row is the ROLLUP grand total. web is most severe by critical rate at 50%, while auth has three warnings. Detail and overall scope are visible in one table.

Model Answer ▼

SELECT
  COALESCE(category, '(ALL)') AS category,        -- Turn the grand-total category=NULL into (ALL)
  COUNT(*) AS total_events,                       -- Total events (denominator)
  COUNT(*) FILTER (WHERE amount >= 300) AS critical,                  -- Critical count
  COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300) AS warning,  -- Warning count
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount >= 300) / COUNT(*)
  , 1) AS crit_pct
FROM event_log
GROUP BY ROLLUP (category)                       -- Category rows plus one grand total
ORDER BY GROUPING(category), category;            -- Detail (0) first, grand total (1) last

/*
  Execution order (logical SQL evaluation order):
  1. FROM event_log               → Read the rows
  2. GROUP BY ROLLUP (category)   → Group by category and grand total
  3. COUNT(*) and FILTER           → Evaluate aggregate functions
  4. SELECT                        → Evaluate columns (crit_pct and label)
  5. ORDER BY GROUPING, category   → Sort and return the output
*/

Explanation (table transitions & key points) ▼

SELECT
  COALESCE(category, '(ALL)') AS category,
  COUNT(*) AS total_events,
  COUNT(*) FILTER (WHERE amount >= 300) AS critical,
  COUNT(*) FILTER (WHERE amount >= 200 AND amount < 300) AS warning,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount >= 300) / COUNT(*)
  , 1) AS crit_pct
FROM event_log
GROUP BY ROLLUP (category)
ORDER BY GROUPING(category), category;

LEGEND ▼

Rows read / loaded

① FROM event_log (12 rows) — Red=critical / orange=warning ▼

FROM event_logTwelve events across three categories. Define amount>=300 as critical and 200–299 as warning. Use these detail rows to build both severity breakdowns by category and one overall total in a single query.

1 / 6

category	dt	amount
api	01-01	350
api	01-02	120
api	01-03	130
api	01-04	95
auth	01-01	260
auth	01-02	280
auth	01-03	240
auth	01-04	90
web	01-01	100
web	01-02	250
web	01-03	300
web	01-04	320

12 rows read (red=crit/orange=warn)

LEARNING POINTS

Use multiple FILTERs to count severity breakdowns at once: Placing COUNT(*) FILTER (WHERE amount >= 300) beside ... (WHERE amount >= 200 AND amount < 300) produces critical and warning counts from each group together. The advanced form simply places one FILTER per severity level.

ROLLUP puts detail and grand total in the same result: GROUP BY ROLLUP (category) generates ordinary category rows plus one row aggregating everything. Without a separate total query, one query creates a dashboard table containing both detail and overall scope. Multiple columns can create subtotal levels.

Distinguish a ROLLUP NULL with COALESCE and GROUPING: The grouping key of a ROLLUP grand-total row is NULL. Label it with COALESCE(category,'(ALL)') and order it with GROUPING(category) (detail=0, total=1). The key is distinguishing NULL as "missing data" from NULL as "a total row".

ANTI-PATTERNS

Append the grand total with a separate query and UNION: Joining a detail query and a total query with UNION ALL is verbose and duplicates aggregate definitions, creating a source of mistakes. When you need detail plus total, use one ROLLUP.

Forget the warning upper bound and double-count critical events: FILTER (WHERE amount >= 200) also includes critical events (≥300), so the counts overlap. For a severity interval, close the upper bound with amount >= 200 AND amount < 300.

IN PRACTICE: From detection to a summary that reaches decision-makers — Q6–Q10 capstone

The advanced section covered: ① rolling z-scores with dynamic baselines (Q6), ② persistence with Gaps & Islands (Q7), ③ seasonal adjustment with a LAG offset (Q8), and ④ severity classification with composite signals (Q9). This question's ⑤ multiple FILTERs + ROLLUP consolidates those detection results into a single summary showing category detail and the grand total at a glance. No matter how advanced detection becomes, what moves people and executives is one aggregated, prioritized table that shows the whole picture. After learning syntax in the fundamentals and practical detection design in the advanced section, you can build production-grade anomaly monitoring with SQL alone.

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.