Recap — Flagging Outliers

"Our signups dropped 30% yesterday — is that anomalous?" The PM is asking you to make a yes/no call from a single number. SQL has all you need.

Pattern 1 — Z-score anomalies (normal-ish data)

Z-score = how many standard deviations from the mean. |Z| > 2 is the conventional flag.

WITH stats AS (
    SELECT
        AVG(daily_signups) AS mean_signups,
        STDDEV(daily_signups) AS std_signups
    FROM daily_signups
    WHERE signup_date >= CURRENT_DATE - INTERVAL '90 days'
      AND signup_date <  CURRENT_DATE
)
SELECT
    d.signup_date,
    d.daily_signups,
    (d.daily_signups - s.mean_signups) / s.std_signups AS z_score,
    CASE
        WHEN ABS((d.daily_signups - s.mean_signups) / s.std_signups) > 2 THEN 'ANOMALY'
        ELSE 'normal'
    END AS flag
FROM daily_signups d, stats s
WHERE d.signup_date >= CURRENT_DATE - INTERVAL '30 days';

Works when signups are roughly normally distributed. Less reliable for skewed metrics.

Pattern 2 — IQR anomalies (skewed data)

For skewed metrics (revenue, latency, LTV), IQR is the more honest tool.

WITH bounds AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY daily_signups) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY daily_signups) AS q3
    FROM daily_signups
    WHERE signup_date >= CURRENT_DATE - INTERVAL '90 days'
),
fences AS (
    SELECT q1, q3, q3 - q1 AS iqr,
           q1 - 1.5 * (q3 - q1) AS lower_fence,
           q3 + 1.5 * (q3 - q1) AS upper_fence
    FROM bounds
)
SELECT
    d.signup_date,
    d.daily_signups,
    CASE
        WHEN d.daily_signups < f.lower_fence OR d.daily_signups > f.upper_fence THEN 'ANOMALY'
        ELSE 'normal'
    END AS flag
FROM daily_signups d, fences f;

IQR uses percentiles, so outliers (whales, traffic spikes) don't distort the bounds the way they distort std.

Pattern 3 — Rolling z-score (seasonality-aware)

For data with trend or weekly seasonality, a fixed baseline misleads. Use a rolling window:

SELECT
    signup_date,
    daily_signups,
    AVG(daily_signups) OVER (
        ORDER BY signup_date
        ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    ) AS rolling_mean,
    STDDEV(daily_signups) OVER (
        ORDER BY signup_date
        ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
    ) AS rolling_std,
    (daily_signups - AVG(daily_signups) OVER (ORDER BY signup_date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING))
        / NULLIF(STDDEV(daily_signups) OVER (ORDER BY signup_date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING), 0) AS rolling_z
FROM daily_signups
ORDER BY signup_date;

The 1 PRECEDING is important — exclude today from the baseline, otherwise today's anomaly partially defines what "normal" is.

When to use which

Metric type	Use
Daily signups (stable trend)	Z-score
Daily revenue (whales drag mean)	IQR
Daily traffic with weekly seasonality	Rolling z-score with seasonal baseline
Long-tailed, no trend	IQR on log(x)

The "what's the threshold" question

|Z| > 2 flags ~5% of days as anomalies if the data is truly normal. |Z| > 3 flags ~0.3%. Pick by tolerance for false positives:

• Slack alerting → use |Z| > 3 (fewer false positives, less alert fatigue)
• Daily standup discussion → |Z| > 2 (more sensitive)

Takeaway

Most analysts default to "30% below average" or eyeballing. Replacing that with z-scores or IQR is a small change that makes the work look much more rigorous — and is the right answer to the "how would you detect anomalies?" interview question.