Recap — Flagging Outliers

# 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. ```sql 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 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. ```sql 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.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: ```sql 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.