z-scores and day-over-day changes are strong for one large point anomaly, but they miss gradual drift: a slightly high value every day. Accumulating (running total) each day's deviation from the target reveals the buildup of small biases.

SUM(amount - 100) OVER (
  ORDER BY dt
  ROWS UNBOUNDED PRECEDING   -- = ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
-- Running total from the first row through the current row
-- With only ORDER BY and no frame, the default is RANGE UNBOUNDED PRECEDING …

From daily_sales (with a daily target of 100), calculate each day's target deviation (daily_dev = amount − 100) and its cumulative value (cum_dev); assign drift_flag 'drift_alert' when cum_dev exceeds 20 and 'normal' otherwise. Return dt, amount, daily_dev, cum_dev, drift_flag in ascending dt order.

Every day is within a few percent of the previous day and looks normal in isolation. But every value remains above target, so the deviations accumulate.

Expected output (ascending dt):

Although no isolated value is anomalous, cumulative deviation reaches 21 on 01-07 and crosses the threshold of 20. This detects a sustained shift that point-anomaly detection misses.

SELECT
  dt, amount,
  amount - 100 AS daily_dev,          -- Daily deviation from the target (100)
  SUM(amount - 100) OVER (            -- Cumulative deviation (running total)
    ORDER BY dt
    ROWS UNBOUNDED PRECEDING            -- First row through current row
  ) AS cum_dev,
  CASE
    WHEN SUM(amount - 100) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) > 20
      THEN 'drift_alert'
    ELSE 'normal'
  END AS drift_flag
FROM daily_sales
ORDER BY dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM daily_sales         → Read the rows
  2. amount - 100             → Format the values
  3. SUM(...) OVER (...)      → Evaluate the window function (preserve row count)
  4. CASE WHEN cum_dev > 20   → Format the values
  5. SELECT / ORDER BY dt     → Sort and return the output
*/

Measuring anomalies against a company-wide average fails when stores of different sizes are mixed. A high sale at a small store can appear normal against a large store's baseline, and vice versa. Measure anomalies within the same peer group. PARTITION BY splits a window by group, attaching each row's own group aggregate.

AVG(amount)    OVER (PARTITION BY store_id)  -- Average for each store
STDDEV(amount) OVER (PARTITION BY store_id)  -- Standard deviation for each store
-- Unlike GROUP BY, rows are not collapsed; group aggregates run alongside each row

From store_sales, calculate each store's average and standard deviation, then calculate each row's z-score = (amount − store average) / store standard deviation; assign status 'anomaly' when |z| is at least 1.5 and 'normal' otherwise. Return store_id, dt, amount, store_avg, store_std, z_score, status, ordered by store_id and then dt. Round average, standard deviation, and z-score to two decimal places.

Store A is normally near 100, with 300 on 01-05 standing out. Store B is consistently near 500. Using the company-wide average (=320) makes A's ordinary values look abnormally low and makes A's 300 less conspicuous. The baseline must be within each store.

Expected output (ascending store_id, ascending dt):

Under A's baseline (average 140, SD 89.5), only 01-05 (300) is anomalous at z=+1.79. B uses a different baseline (average 500, SD 7.9), so every row is normal. Each segment has its own anomaly scale.

SELECT
  store_id, dt, amount,
  ROUND(AVG(amount)    OVER w, 2) AS store_avg,  -- Average within the store
  ROUND(STDDEV(amount) OVER w, 2) AS store_std,  -- Standard deviation within the store
  ROUND(
    (amount - AVG(amount) OVER w)
    / NULLIF(STDDEV(amount) OVER w, 0)      -- Avoid division by zero when SD=0
  , 2) AS z_score,
  CASE
    WHEN ABS(
      (amount - AVG(amount) OVER w)
      / NULLIF(STDDEV(amount) OVER w, 0)
    ) >= 1.5 THEN 'anomaly'
    ELSE 'normal'
  END AS status
FROM store_sales
WINDOW w AS (PARTITION BY store_id)     -- Split and reuse the window for each store
ORDER BY store_id, dt;

/*
  Execution order (logical SQL evaluation order):
  1. FROM store_sales                  → Read the rows
  2. WINDOW w (PARTITION BY store_id)  → Define the windows
  3. AVG/STDDEV OVER w                 → Evaluate window functions (preserve row count)
  4. (amount-avg)/NULLIF(std,0)        → Calculate z-score
  5. CASE WHEN ABS(z) is at least 1.5  → Decide anomaly status
  6. SELECT / ORDER BY store_id, dt    → Sort and return the output
  */

Duplicate records—such as the same transaction being counted twice—are one of the most common data-quality anomalies. A single row cannot reveal them; you must count how often the same key occurs. The essential tools are GROUP BY (collapse rows to aggregate) and HAVING (filter the aggregated result).

WHERE  -- Filter individual rows before aggregation (COUNT cannot be used here)
GROUP BY txn_id   -- Collapse identical txn_id values into one group
HAVING COUNT(*) > 1  -- Filter each group after aggregation

From txn_log, extract only transactions whose txn_id is recorded two or more times (duplicates), calculating their occurrence count and total amount. Return txn_id, occurrences, total_amount, ordered by descending occurrences and ascending txn_id.

T-101 occurs three times and T-102 twice, so both are duplicates. T-103 and T-104 occur once and are normal. The same transaction ID recorded at the same amount across several days suggests double counting.

Expected output (descending occurrences, ascending txn_id):

T-103 and T-104, which occur only once, are removed by HAVING COUNT(*) > 1; only the two duplicates remain. total_amount is the amount counted more than once (the overstatement).

SELECT
  txn_id,
  COUNT(*)      AS occurrences,   -- Number of rows in the group = occurrence count
  SUM(amount)  AS total_amount  -- Amount total within the group
FROM txn_log
GROUP BY txn_id              -- Collapse each txn_id into one group
HAVING COUNT(*) > 1          -- After aggregation, keep only groups occurring twice or more
ORDER BY occurrences DESC, txn_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM txn_log           → Read the rows
  2. (no WHERE)             → Filter rows
  3. GROUP BY txn_id        → Group rows
  4. COUNT(*) / SUM(amount) → Evaluate aggregate functions
  5. HAVING COUNT(*) > 1    → Filter groups
  6. SELECT / ORDER BY      → Sort and return the output
*/

The final anomaly-detection step is to aggregate findings into alerts. “For each category, how many days were anomalous and what percentage of all days? Notify only categories with enough anomalous days.” COUNT(*) FILTER (WHERE ...) plus GROUP BY ... HAVING writes this combination of conditional counting and group filtering in one query (a synthesis of Q6–Q9).

COUNT(*) FILTER (WHERE amount > 200)  -- Count only rows matching the condition
COUNT(*)                              -- Count all rows (the denominator)
-- Within one group, aggregate all rows and anomalies simultaneously

From metrics, daily measurements by category, define amount > 200 as anomalous and aggregate each category's total days (total_days), anomalous days (anomaly_days), and anomaly rate percent (anomaly_pct). Keep only categories with at least two anomalous days as notification targets. Return category, total_days, anomaly_days, anomaly_pct, ordered by descending anomaly_days and ascending category. Round pct to one decimal place.

Anomalies (amount>200) are web: 2 days, auth: 3, api: 1, and cdn: 0. Filtering to at least two anomalous days leaves only web and auth as notification targets.

Expected output (descending anomaly_days, ascending category):

api (one anomalous day) and cdn (zero) are excluded by HAVING anomaly_days >= 2. auth is anomalous every day and takes top priority, followed by web: a prioritized list of categories that need notification.

SELECT
  category,
  COUNT(*)                                   AS total_days,  -- Total days in the category (denominator)
  COUNT(*) FILTER (WHERE amount > 200)     AS anomaly_days,  -- Count only anomalous days
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE amount > 200)
    / COUNT(*)                                -- Anomaly rate = anomalous days / total days
  , 1) AS anomaly_pct
FROM metrics
GROUP BY category                            -- Aggregate by category
HAVING COUNT(*) FILTER (WHERE amount > 200) >= 2  -- Notify only when at least two days are anomalous
ORDER BY anomaly_days DESC, category;

/*
  Execution order (logical SQL evaluation order):
  1. FROM metrics                       → Read the rows
  2. GROUP BY category                  → Group rows
  3. COUNT(*) and COUNT(*) FILTER(...)  → Evaluate aggregate functions
  4. ROUND(100.0*anomaly/denominator,1) → Format the value
  5. HAVING COUNT(*) FILTER(...) >= 2   → Filter groups
  6. SELECT / ORDER BY anomaly_days DESC → Sort and return the output
*/