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

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

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:

WITH scored AS (<above query>)
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.

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:

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 < q1 - 1.5*(q3-q1) OR signups > q3 + 1.5*(q3-q1) THEN 'ANOMALY' END AS flag
FROM bounds
WHERE date >= CURRENT_DATE - INTERVAL '90 days';

(Note: not all databases support PERCENTILE_CONT as a window function; some require a CTE.)

Follow-up 2: "What if we have a known launch on day X that we expect to spike signups?"

Hardcode an exclusion list or use an events(date, event_type) table joined in to know which days are "expected to be different":

SELECT s.* FROM scored s LEFT JOIN known_events e ON e.date = s.date
WHERE ABS(s.z) > 2 AND e.date IS NULL;

Flag only anomalies that aren't on a known-event day.

Follow-up 3: "What if data is missing for some days?"

The rolling window's ROWS clause is row-based — it doesn't know about calendar days. With missing days, the 28-row window spans more than 28 calendar days. Either:

• LEFT JOIN to a date spine to fill missing days with signups = 0, or
• Use RANGE BETWEEN INTERVAL '28 days' PRECEDING AND INTERVAL '1 day' PRECEDING (date-aware window).

Self-check MCQs