Advanced SQL: Metric Monitoring, Anomaly Detection, and Data Correctness at Scale Questions

Direct answer

Instead of a full statistical model, compare a segment's recent rolling average to its own prior window using a simple ratio or percent-change rule (for example: flag if this week's average is more than 50% below, or more than 1.5x above, the prior window). This is faster to reason about and explain to stakeholders than a z-score, but it needs a minimum-volume guard or it becomes a noise generator on small segments.

Structured elaboration

• The rule: LAG the prior window's value, compute the ratio or percent difference, and flag against a fixed multiplier threshold rather than a standard-deviation-based one.
• Why a minimum-volume guard is required: a segment with 3 events last week and 1 this week has "dropped 67%", which is a meaningless statement about a metric that small. Attach a floor (e.g. the prior window must have at least some minimum count) before evaluating the ratio at all, exactly analogous to the minimum-history guard on a z-score check.
• When to reach for this instead of a z-score model: this rule is easier to explain in a sentence ("cut in half week over week"), cheaper to compute (no variance estimation), and reasonable when you mainly care about large, obvious swings rather than subtle statistical drift. A z-score-based approach is better when the metric has real day-to-day noise you need to calibrate against, or when different segments have very different natural volatility that a single fixed percentage threshold would treat unfairly.

Worked example

SELECT segment, week_start, weekly_count,
  LAG(weekly_count) OVER (PARTITION BY segment ORDER BY week_start) AS prev_week,
  ROUND(100.0 * (weekly_count - LAG(weekly_count) OVER (PARTITION BY segment ORDER BY week_start))
        / NULLIF(LAG(weekly_count) OVER (PARTITION BY segment ORDER BY week_start), 0), 1) AS pct_change,
  (LAG(weekly_count) OVER (PARTITION BY segment ORDER BY week_start) >= 50   -- minimum-volume guard
   AND weekly_count < 0.5 * LAG(weekly_count) OVER (PARTITION BY segment ORDER BY week_start)) AS flagged
FROM seg_activity
ORDER BY segment, week_start;

On two segments over five weeks: checkout goes 500, 480, 510, 495, then drops to 200 (a genuine 59.6% week-over-week drop) and is correctly flagged. rare_flow goes 6, 4, 5, 3, then 1 (a 66.7% drop by the same percentage math) but is correctly NOT flagged, because its prior-week volume of 3 never clears the minimum-volume floor of 50. Without that floor, rare_flow would fire an alert every time it moves by one or two events, which is exactly the noisy-low-traffic-segment failure mode this guard exists to prevent.

Trade-offs & pitfalls

• A fixed percentage threshold is not scale-invariant: 50% is a sensible bar for a segment doing hundreds of events a week, but may be far too loose (or too strict) for a segment two orders of magnitude larger or smaller. If segments vary widely in scale, either use different thresholds per segment tier or fall back to a variance-aware method like the z-score approach for the largest segments.
• This method has no built-in seasonality awareness: comparing this week only to last week, rather than to several prior same-period windows, will fire on a normal, recurring cyclical pattern (a predictable Monday dip, for example) exactly as readily as on a genuine problem.
• Investigation, not just detection: once a segment is flagged, the practical next step is a diagnostic checklist (instrumentation change, upstream data-quality break, a real product regression) rather than treating the SQL flag itself as the final answer.

Direct answer

Average revenue per user (ARPU) for a period and region is total billed revenue divided by the count of subscribers active at any point in that period, where "active" means the subscription's date range overlaps the period rather than requiring the subscription to have started exactly within it.

Structured elaboration

• Active-in-period condition: a subscription counts as active in a month if start_date <= period_end AND (end_date IS NULL OR end_date >= period_start). This correctly includes subscriptions that started before the period and are still open (end_date IS NULL), and excludes ones that ended before the period began.
• ARPU formula: SUM(monthly_price) / COUNT(*), grouped by region, over the set of subscriptions meeting the active-in-period condition.
• Assumptions to state out loud: whether a subscriber who upgraded or downgraded mid-month is counted at their price at query time or something else (this simple version assumes one row per subscription with a single monthly_price), and whether partial-month subscriptions (started or ended mid-period) are billed pro-rated or at the full monthly rate; the simple version here assumes full monthly billing regardless of exact start/end day within the period, which is a common simplification worth flagging rather than hiding.

Worked example

SELECT region,
  SUM(monthly_price) AS total_billed,
  COUNT(*) AS active_subscribers,
  ROUND(SUM(monthly_price) / COUNT(*), 2) AS arpu
FROM subscriptions
WHERE start_date <= '2025-01-31'
  AND (end_date IS NULL OR end_date >= '2025-01-01')
GROUP BY region;

Given three US subscriptions ($20/mo since Nov 2024 still active, $15/mo that started and ended within January, $25/mo that started in January and is still active) and one UK subscription ($18/mo, active since June 2024): the US group has total_billed = 60.0, active_subscribers = 3, arpu = 20.0; the UK group has arpu = 18.0. Note that the $15/mo subscription which started AND ended entirely inside January is still counted as a full active subscriber for the month under this definition, contributing its full monthly price even though it wasn't active the whole month; that's exactly the kind of simplification the assumptions above need to call out.

Trade-offs & pitfalls

• The overlap condition, not an equality on start_date, is what makes this correct: a common mistake is to filter start_date BETWEEN period_start AND period_end, which would wrongly exclude every subscriber who started before the period and just happens to still be active.
• NULL end_date means "still active", not "unknown"; treating a NULL end date as excluding the row (for example with a naive end_date >= period_start that silently drops NULL rows, since SQL comparisons against NULL are never true) is a classic bug that undercounts active subscribers.
• This basic version ignores mid-period plan changes and proration entirely; that's an acceptable simplification for a definitional first pass, but should be named as a known limitation rather than left implicit.

Direct answer

Compute the rate itself with a straightforward aggregate query, then test whether the week-over-week change is statistically significant with a two-proportion z-test rather than eyeballing the raw percentage difference. A seller with only a handful of orders can swing 10 points in a week from noise alone; a seller with hundreds of orders moving 2 points might be a real signal.

Structured elaboration

• Step 1, the rate: cancellation_rate = COUNT(CASE WHEN status = 'canceled' THEN 1 END) / COUNT(*), filtered to the last 90 days, grouped by seller_id, with sellers under 50 orders excluded (the corpus-level floor the question specifies, itself a coarse version of the same minimum-volume idea used to guard rate metrics elsewhere). As a runnable query:

SELECT seller_id,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled_orders,
  ROUND(1.0 * SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) / COUNT(*), 4) AS cancellation_rate
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY seller_id
HAVING COUNT(*) >= 50
ORDER BY seller_id;

Run against synthetic data (a seller with 60 orders in the trailing 90 days and 6 cancellations, a seller with only 20 recent orders, and a seller with 60 orders that are all older than 90 days), this returns only the first seller, with cancellation_rate = 0.10; the other two are correctly dropped, one by the 50-order volume floor and one by the 90-day window.

• Step 2, is a week-over-week change significant: for two independent proportions $p_1 = x_1/n_1$ (this week) and $p_2 = x_2/n_2$ (last week), pool them into a single estimate under the null hypothesis that the true rate did not change:
  $\hat{p} = \frac{x_1 + x_2}{n_1 + n_2}$
  $SE = \sqrt{\hat{p}(1-\hat{p})\left(\frac{1}{n_1} + \frac{1}{n_2}\right)}$
  $z = \frac{p_1 - p_2}{SE}$
  In plain language, this test asks: if this seller's TRUE cancellation rate had not actually changed, how surprising would it be to see a gap this large purely from the random luck of which orders happened to land in each week's sample? That "how surprising" number is the p-value -- the smaller it is, the less plausible it is that pure chance alone produced the observed gap. The 1.96 cutoff comes from the standard normal distribution: under the null hypothesis (that the true rate did not change), $z$ is approximately normally distributed, and about 95% of that distribution's area falls between -1.96 and +1.96. So a $|z|$ beyond roughly 1.96 means the observed gap falls in the most extreme 5% of what pure chance alone would produce -- the conventional (if somewhat arbitrary) bar for calling a result "statistically significant", which is the same thing as saying it corresponds to a two-tailed p-value under 0.05.
• Why not just compare raw percentages: a seller going from 10% to 12% cancellations on 50 orders a week is well within normal sampling noise; the same 2-point move on 5,000 orders a week is very unlikely to be noise. The test naturally accounts for this because the standard error shrinks as $n$ grows.

Worked example

A seller has 200 orders and 20 cancellations this week ($p_1 = 0.10$), and 180 orders and 36 cancellations last week ($p_2 = 0.20$):
$\hat{p} = \frac{20+36}{200+180} = \frac{56}{380} \approx 0.1474$
$SE = \sqrt{0.1474 \times 0.8526 \times \left(\frac{1}{200}+\frac{1}{180}\right)} \approx 0.0364$
$z = \frac{0.10 - 0.20}{0.0364} \approx -2.75$
That corresponds to a two-tailed p-value of about 0.006, well under a typical 0.05 significance bar, so this seller's change is a genuine signal worth surfacing, not noise. By contrast, the same 10-point swing on a seller with only 50 orders each week ($x_1=5, x_2=10$) gives $\hat p \approx 0.15$, $SE \approx 0.071$, $z \approx -1.4$, which does not clear a 0.05 threshold and should not be surfaced as a real signal.

Trade-offs & pitfalls

• Multiple comparisons: running this test across every seller every week means many tests are being run simultaneously, and a 5% false-positive rate per test adds up to many false alarms across thousands of sellers. A stricter threshold, or a correction such as controlling the false discovery rate -- the fraction of all the sellers flagged as "significant" that are actually false alarms, once thousands of tests are running at once rather than just one -- is the honest answer once volume is high; don't present a bare $p<0.05$ per-seller as if it were the whole story at that scale.
• Independence assumption: the two-proportion test assumes each week's orders are independent draws, which breaks down if the same customers or the same handful of large orders dominate both weeks.
• Practical significance still matters: a statistically significant 0.5-point move on a huge seller may not be operationally meaningful even though the test says it's real; report both the p-value and the effect size.

Direct answer

A naive rolling z-score (mean and standard deviation over the trailing N days, current day included or not) breaks down whenever the metric has weekly seasonality, because it compares a day to a mixed population of weekdays and weekends rather than to its own kind of day. The fix is to compute the baseline per day-of-week: compare a Monday only to prior Mondays.

Structured elaboration

• Naive approach: AVG/STDDEV_SAMP over ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, ordered by date, then z = (value - roll_mean) / roll_std.
• Why it breaks: a 7-day window spanning both weekday and weekend values estimates a standard deviation that reflects the SIZE of the seasonal swing, not genuine day-to-day noise. That inflated standard deviation does two things at once: it makes structurally-low weekend days score a moderate, harmless-looking negative z (a false signal of concern where none exists), and it makes a real anomaly on a weekday look small relative to that inflated spread (a missed true anomaly).
• Fix: partition the window by day-of-week, ordered by date, so each day is compared only to its own recent history of the same weekday: PARTITION BY EXTRACT(dow FROM day) ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING.
• Remaining pitfalls even after the fix: a day-of-week baseline still needs several prior instances of that weekday before its own standard deviation is meaningful (same minimum-history guard as any rolling statistic), and it will not by itself catch a genuine change that happens to be a full week long, since day-of-week baselines are blind to same-weekday drift over a multi-week span.

Worked example

Five weeks of a metric with clean weekly seasonality (weekdays around 100-104, weekends around 40-42) and ONE planted real anomaly: a Friday reading of 130 where every other Friday in the series was 100-104. Concretely:

Naive rolling 7-day z-score on that Friday:

SELECT day, metric_value,
  AVG(metric_value) OVER w AS roll_mean,
  STDDEV_SAMP(metric_value) OVER w AS roll_std,
  (metric_value - AVG(metric_value) OVER w) / STDDEV_SAMP(metric_value) OVER w AS z
FROM daily_metric
WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

gives roll_mean = 89.00, roll_std = 33.98, z = 1.21 -- computed directly from the trailing 7 calendar days ending on that Friday (week 4's Sat, Sun: 41, 42, then week 5's Mon-Fri: 104, 102, 103, 101, 130): mean = (41+42+104+102+103+101+130)/7 = 623/7 = 89.00, and the sample standard deviation of those same 7 values is 33.98, comfortably under any reasonable alert threshold. The genuine 25-30% spike is missed, because the naive window's standard deviation is inflated to 33.98 by mixing weekday and weekend values.

Day-of-week-adjusted baseline on the identical Friday:

SELECT day, metric_value,
  AVG(metric_value) OVER w AS dow_mean,
  STDDEV_SAMP(metric_value) OVER w AS dow_std,
  (metric_value - AVG(metric_value) OVER w) / STDDEV_SAMP(metric_value) OVER w AS z_dow
FROM daily_metric
WINDOW w AS (PARTITION BY dayofweek(day) ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING)

gives dow_mean = 102.00, dow_std = 1.826, z_dow = 15.34 -- from the prior 4 Fridays in the table (weeks 1-4: 101, 103, 100, 104): mean = 408/4 = 102.00, sample standard deviation 1.826, and z_dow = (130 - 102.00) / 1.826 = 15.34. Comparing only to prior Fridays gives a tight, honest standard deviation and correctly flags this as a dramatic outlier. On week 5's Saturday (41), an ordinary non-anomalous weekend day in the same table: the naive method's trailing 7-day window shifted one day later ([42, 104, 102, 103, 101, 130, 41], the same mean 89.00 and std 33.98 as above) reports z = (41 - 89.00) / 33.98, which works out to roughly -1.41 (looks like a mild, spurious negative signal), while the day-of-week method, using the prior 4 Saturdays (41, 40, 42, 41, mean 41.00, std 0.816), reports z_dow = (41 - 41.00) / 0.816 = 0.00 exactly (correctly recognizing that value as completely normal for that day type).

Trade-offs & pitfalls

• The naive method is not just "less accurate", it fails in both directions at once: false positives on structurally low days, false negatives on real anomalies, because both failures share the same root cause (an inflated, conflated standard deviation).
• Day-of-week baselining costs sample size: with only 5 weeks of history, the day-of-week window has just 4 prior points, which is a thin standard deviation estimate. In practice you want at least 6-8 weeks of history before trusting this baseline, and you should still apply a minimum-history guard exactly as in a simpler per-entity outlier check.
• Other seasonality shapes (monthly billing cycles, holiday effects) need the equivalent treatment: partition by whatever unit of seasonality actually governs the metric, not just day-of-week by default.

Direct answer

Compute month-to-date revenue, period-over-period growth, and any rolling average in the SQL/warehouse layer with window functions whenever the number is reused across multiple dashboards or needs to be fast and consistent; leave it to the visualization layer only when it is genuinely a single-dashboard, exploratory, or user-parameterized calculation that would otherwise force a combinatorial explosion of precomputed variants in SQL.

Structured elaboration

• Push to SQL when: the same number needs to appear identically on several dashboards (a single source of truth avoids two tools disagreeing on the definition of "month-to-date"), when the underlying table is too large for the visualization tool to aggregate interactively, or when the logic (a rolling average, a period-over-period join) is intricate enough that re-deriving it correctly in every dashboard tool invites drift.
• Leave to the visualization layer when: the user needs to interactively change a parameter (like the moving-average window size) without re-running a query for every possible value, or when the calculation is exploratory and one-off rather than a governed metric.
• Reusability vs latency trade-off: a precomputed SQL view refreshed on a schedule is fast to query but only as fresh as its last refresh; a viz-layer calculation over live data is always current but recomputes on every view and does not scale past what the tool can pull and process client-side.

Worked example

A parameterized rolling average, where the user picks the window size interactively (say between 3 and 30 days), illustrates the trade-off concretely. Precomputing SQL views for every possible window size is wasteful and still incomplete; computing the SAME single window size in every visualization tool that needs it (Tableau, Looker, a notebook) risks each one rounding boundary dates or handling missing days slightly differently. The practical middle ground: compute the DAILY raw values feeding the average in SQL (this is the expensive, reusable part: joins, filters, deduplication), then let the parameterized window itself (AVG(value) OVER (ORDER BY day ROWS BETWEEN :n PRECEDING AND CURRENT ROW), with :n supplied at query time or done client-side over the pre-aggregated daily series) live closer to the user. This way the governed, reusable logic (what counts as a valid day's value) is centralized, while the interactive parameter (window size) stays cheap to vary.

Trade-offs & pitfalls

• "Just put it in SQL" is not automatically correct: over-centralizing every possible metric variant into materialized SQL views creates a maintenance burden of near-duplicate views that drift out of sync with each other just as easily as duplicated visualization-layer logic does.
• Governance vs flexibility is the real axis, not "SQL is more correct than a BI tool". A number that must be provably consistent everywhere belongs in SQL; a number a single analyst is exploring belongs wherever is fastest for them to iterate.
• Refresh cadence needs to be explicit either way: a rolling average computed on stale, pre-aggregated daily data is only as current as that daily table's last load, regardless of which layer does the final rolling-window math.