Case 4 — Detect Anomalous Days in Daily Signups

# Case 4 — Detect Anomalous Days in Daily Signups > *"From `daily_signups(date, signups)`, flag the days in the last 90 days where signups were anomalous. Defend your choice of threshold."* The interviewer cares about three things: (1) you build something defensible, (2) you don't use a fixed baseline that includes the day you're testing, (3) you can explain why your threshold makes sense. --- ## The naive (wrong) answer ```sql WITH stats AS ( SELECT AVG(signups) AS mean_sig, STDDEV(signups) AS std_sig FROM daily_signups WHERE date >= CURRENT_DATE - INTERVAL '90 days' ) SELECT d.date, d.signups, (d.signups - s.mean_sig) / s.std_sig AS z_score FROM daily_signups d, stats s WHERE date >= CURRENT_DATE - INTERVAL '90 days'; ``` This is technically correct but has two problems: 1. The baseline includes the days being tested — so a huge spike day partially defines the "normal" mean and std it's being compared against. 2. No seasonality awareness — Monday signups and Sunday signups are very different; a constant baseline treats them the same. ## Better — rolling z-score ```sql SELECT date, signups, AVG(signups) OVER ( ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING ) AS rolling_mean, STDDEV(signups) OVER ( ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING ) AS rolling_std, (signups - AVG(signups) OVER (ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)) / NULLIF(STDDEV(signups) OVER (ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING), 0) AS z FROM daily_signups WHERE date >= CURRENT_DATE - INTERVAL '120 days' ORDER BY date; ``` Two changes: - `28 PRECEDING AND 1 PRECEDING` — 28-day window excluding today. The day being tested never contributes to its own baseline. - The outer WHERE pulls 120 days so the rolling window for day -90 has 28 days of history. Then filter: ```sql WITH scored AS ( ) SELECT date, signups, ROUND(z::numeric, 2) AS z_score, 'ANOMALY' AS flag FROM scored WHERE date >= CURRENT_DATE - INTERVAL '90 days' AND ABS(z) > 2 ORDER BY date; ``` ## Even better — handle weekly seasonality If signups have a clear weekly pattern (high on Tuesday, low on Sunday), a 28-day rolling mean misses that. Two fixes: **Fix A**: Compare each day to the same-day-of-week from previous weeks. ```sql WITH dow_baseline AS ( SELECT date, signups, AVG(signups) OVER ( PARTITION BY EXTRACT(DOW FROM date) ORDER BY date ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING ) AS dow_mean, STDDEV(signups) OVER ( PARTITION BY EXTRACT(DOW FROM date) ORDER BY date ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING ) AS dow_std FROM daily_signups ) SELECT date, signups, (signups - dow_mean) / NULLIF(dow_std, 0) AS z_dow FROM dow_baseline WHERE date >= CURRENT_DATE - INTERVAL '90 days'; ``` Each day's baseline is the last 4 same-days-of-week. Tuesdays compared to Tuesdays. **Fix B**: Decompose into trend + seasonality + residual using STL (out of SQL scope; usually done in Python or as a pre-aggregated table). For an interview, Fix A is the impressive answer that's still pure SQL. ## Defending your threshold The interviewer will ask: *"Why |z| > 2 and not |z| > 3?"* The honest answer: - |z| > 2 flags ~5% of days as anomalies assuming normality. That's ~4-5 days out of the last 90. - |z| > 3 flags ~0.3% — about 1 day in 90 days. Less noise but misses milder anomalies. - Pick based on the use case: Slack alerts → |z| > 3 (reduce alert fatigue); daily standup → |z| > 2 (catch more). The wrong answer: "Because that's the standard." Show you understand the tradeoff. ## What to say at the end > **You**: "I went with a rolling 28-day z-score excluding the test day, with the option to add day-of-week partitioning if there's weekly seasonality. The threshold of |z| > 2 catches ~5% of days; |z| > 3 catches ~0.3%. The choice depends on whether the downstream consumer is a Slack alert (use 3) or a daily review (use 2). I'd also note that z-scores assume the underlying distribution is roughly normal — if signups are bounded by population effects or have big launch spikes, an IQR-based detector is more robust." ## Common follow-ups ### Follow-up 1: "What if the underlying distribution is not normal?" Switch to IQR: ```sql WITH bounds AS ( SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY signups) OVER ( ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING ) AS q1, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY signups) OVER ( ORDER BY date ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING ) AS q3, date, signups FROM daily_signups ) SELECT date, signups, CASE WHEN signups q3 + 1.5*(q3-q1) THEN 'ANOMALY' END AS flag FROM bounds WHERE date >= CURRENT_DATE - INTERVAL '90 days';