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

Direct answer

Maintain the cumulative metric as an incrementally-updated table keyed on the entity (for example, per user), where each run only touches the partitions affected by NEW or CORRECTED data since the last run, using a merge/upsert rather than recomputing the running total from the full history every time.

Structured elaboration

• Table design: partition (or cluster) the maintained cumulative table by the entity key and, if the source data itself is naturally date-partitioned, by date as well, so a given run only needs to read the partitions that actually changed.
• Merge/upsert pattern: for each entity affected by new data since the last run, recompute that entity's cumulative value by adding the new increment to its PREVIOUSLY STORED cumulative value, rather than re-summing the entity's entire history. This turns an $O(\text{full history})$ recompute into an $O(\text{new data})$ update.
• Handling corrections to historical partitions: if a correction lands against data from months ago (a late refund, a corrected transaction amount), the incremental approach above breaks, because it assumed only NEW increments need to be added, not that OLD increments might change. The fix: when a correction is detected, recompute the cumulative value starting from the corrected partition forward, not just apply the delta at the current date, since every day's running total downstream of the correction is now stale. This is more expensive than a routine incremental update but still far cheaper than a full history recompute, since it only replays forward from the earliest affected date rather than from the beginning of time.
• Detecting that a correction happened at all: usually requires either an explicit "corrected records" feed from the source system, or a reconciliation check comparing a freshly-computed total for an old period against the currently stored one and flagging a mismatch.

Worked example

Consider a per-user cumulative revenue table maintained incrementally. On a normal day, a new $50 order for user 42 arrives; the maintained pipeline reads user 42's current stored cumulative value (say $1,200), adds $50, and writes back $1,250, touching only that one row. Contrast this with a correction: a $30 refund is discovered against an order from 60 days ago for the same user. A pure "add the new increment" pipeline would incorrectly just subtract $30 from TODAY's cumulative value, leaving every day BETWEEN the refunded order and today reporting a cumulative total that is $30 too high (since the running total at each of those intermediate dates was itself computed by summing everything up to that point, and none of those historical days ever saw the correction). The correct handling replays the cumulative sum forward starting from the date of the original order 60 days ago, correcting that day's cumulative total and every day after it through today, using the corrected order-level data as the source of truth.

Trade-offs & pitfalls

• The single biggest defect in a naive incremental design is exactly the correction case above: an interviewer probing this question is very likely testing whether the candidate's design silently mishandles a correction to old data, not just whether they know how to add up numbers.
• Detecting corrections is often the harder engineering problem than applying them, especially against a source system that doesn't clearly flag which historical records changed; a reconciliation job comparing recomputed vs. stored totals on a sample of old partitions is a common pragmatic mitigation.
• Replaying forward from a correction point is still bounded work (from the correction date to today), not unbounded, which is the entire point of this design relative to a full nightly recompute; but it does mean a correction to very old data is proportionally more expensive to apply than one to recent data.

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

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

Design the pipeline so a metric can be computed against an initial, incomplete view of the data and then CORRECTED as late events arrive, rather than either blocking on a fixed wait period or silently ignoring data that shows up after the fact. The core mechanism is a watermark (a moving cutoff stating "we believe all events up to this point in time have arrived") paired with a bounded backfill window and idempotent writes so reprocessing a partition never double-counts.

Structured elaboration

• Watermarking: track, per source, how late events are typically observed to arrive (for example, "99% of events land within 14 days"), and use that as the watermark lag. Before the watermark passes a given time, that period's metric is provisional; after it passes, treat the metric as settled (though a longer tail of stragglers is still possible and should be handled by the backfill window below, not assumed away).
• Backfill window: reprocess a bounded trailing window (matching the watermark lag) on every run, rather than either reprocessing all history (expensive and unnecessary) or never reprocessing at all (silently wrong once late data lands).
• Idempotent writes: structure the write as an upsert keyed on the natural grain of the metric (for example, (metric_date, dimension)), so re-running the same backfill window twice produces the same result rather than double-adding.
• Scheduling: run the backfill window on a recurring schedule (hourly or daily depending on how time-sensitive the metric is), separate from any real-time or near-real-time provisional view that might also be shown to users with an explicit "not yet final" label.
• The trade-off being decided: how quickly the metric is available (favoring a short watermark, more stale-but-fast results, more revisions later) versus how accurate it is the first time it's shown (favoring a long watermark, fewer revisions, but a longer wait before anything is reported at all). There is no setting that maximizes both; state which one the business needs more and size the watermark accordingly.

Worked example

Consider a rolling metric with a 14-day observed late-arrival tail. A pipeline that recomputes only "yesterday's" partition every day will silently under-report any metric-date whose true final event arrives on day 8, 10, or 14 after that date, because it never revisits that partition again. The corrected design: every daily run reprocesses the trailing 14 days (not just yesterday), using an upsert on (metric_date, dimension_keys) so each of those 14 days' rows are overwritten with the latest, more-complete count rather than inserted a second time alongside the stale row. A metric-date's reported value can therefore change up to 14 times before it stabilizes, which is a DELIBERATE, visible property of the design (worth surfacing to consumers, e.g. with a "last updated" or "as of" timestamp on the metric), not a bug to hide.

Concretely, the idempotent upsert looks like:

INSERT INTO daily_metric_agg (metric_date, dimension, metric_value)
VALUES (DATE '2025-01-01', 'US', 140.0)
ON CONFLICT (metric_date, dimension)
DO UPDATE SET metric_value = EXCLUDED.metric_value;

Running this same statement twice in a row (simulating the 14-day backfill window overlapping itself on consecutive daily runs) leaves exactly one row for (2025-01-01, 'US') holding the latest value, not two rows and not a doubled total, which is the entire point of keying the upsert on the metric's natural grain instead of appending with a plain INSERT.

Trade-offs & pitfalls

• Reprocessing too short a window silently drops the same class of correctness bug it's trying to fix: if the true late-arrival tail is 14 days but the backfill window is only set to 7, events landing on day 8-14 are permanently lost from the metric with no error raised anywhere.
• Idempotency is not automatic just because you used INSERT: a naive re-run that appends rather than upserts on the metric's natural key will double-count every time the backfill window overlaps a previous run, which it always will by design.
• Provisional numbers need explicit labeling if they're user-facing: showing an unlabeled, not-yet-final number next to a settled one from a different period invites a wrong comparison; either delay display until the watermark passes, or clearly mark the number as provisional.

Direct answer

Convert every event timestamp to the RELEVANT local time zone before truncating it to a calendar day, rather than bucketing by the UTC calendar date directly; naive UTC-day bucketing systematically misclassifies events that happen late at night or early in the morning local time into the wrong day, and daylight saving transitions add a once- or twice-a-year edge case on top of the everyday offset problem.

Structured elaboration

• The core bug: CAST(event_ts_utc AS DATE) buckets by the UTC calendar day, which is only correct for users physically in UTC. A user in a timezone several hours behind UTC has evening events land on the "next" UTC day; a user several hours ahead has morning events land on the "previous" UTC day.
• The fix: convert the timestamp to the relevant local time zone FIRST, then truncate to a day: conceptually CAST(event_ts_utc AT TIME ZONE 'UTC' AT TIME ZONE user_tz AS DATE) (exact syntax varies by engine, but the two-step "reinterpret as UTC, then convert to the target zone" pattern is the general shape).
• Which timezone is "relevant" needs an explicit decision: the user's own device/account timezone, the business's home timezone, or a single canonical timezone the whole company reports in. Each is a legitimate choice for different purposes (a global company-wide daily report often uses one canonical timezone deliberately, even though that means SOME users' "day" boundary won't match their own clock) but must be stated, not left implicit.
• Daylight saving transitions: converting through a named time zone identifier (like America/Los_Angeles) rather than a fixed UTC offset (like UTC-8) is what correctly handles the spring-forward/fall-back transition automatically, since the named zone's rules already encode when the offset changes; a fixed-offset conversion will silently misbucket events around the transition dates every year.
• Testing strategy: specifically construct test events that fall in the few hours around midnight local time and around a known daylight saving transition date, and confirm they land in the expected calendar day; these are exactly the cases a naive implementation gets wrong and a representative test suite needs to include on purpose, not by accident.

Worked example

An event recorded at 2025-03-15 02:30:00 UTC for a Pacific-time user: naive UTC-day bucketing assigns it to March 15. Converting through the named zone: 02:30 UTC on March 15 is 19:30 (7:30pm) on March 14 in America/Los_Angeles, so the correctly local-day-bucketed value is March 14, a full day earlier than the naive UTC bucketing would report. A second event at 2025-03-15 10:00:00 UTC converts to 03:00 on March 15 local time, correctly landing on March 15 either way in this particular example, illustrating that the bug doesn't show up on every event, only on the ones near the local midnight boundary, which is exactly why it tends to survive un-caught until someone notices a cohort or daily count that's subtly off by a day for some fraction of users.

As a runnable query performing the actual conversion:

SELECT event_id, user_id, user_tz, event_ts_utc,
  (event_ts_utc AT TIME ZONE 'UTC' AT TIME ZONE user_tz) AS event_ts_local,
  CAST(event_ts_utc AS DATE) AS naive_utc_day,
  CAST((event_ts_utc AT TIME ZONE 'UTC' AT TIME ZONE user_tz) AS DATE) AS correct_local_day
FROM events
ORDER BY event_id;

Against the two events above this returns naive_utc_day = 2025-03-15 / correct_local_day = 2025-03-14 for the first event, and naive_utc_day = correct_local_day = 2025-03-15 for the second, confirming the bug bites only the first event.

Trade-offs & pitfalls

• A cohort or DAU (daily active users) pipeline that buckets by UTC day will have a real, silent misclassification rate for any user population spread across time zones, and the size of that mismatch is easy to underestimate, concentrated in a block of hours whose SIZE equals the magnitude of the user's UTC offset, not just a token hour or two. For a Pacific user (UTC-8), the naive UTC day disagrees with the true local day for the entire 4pm-to-midnight local block (8 of the day's 24 hours); for a user east of UTC the mismatched block instead falls right after local midnight. Because the west-of-UTC block sits inside a typical consumer product's evening traffic peak rather than its quiet hours, the true misclassification share for a US-heavy user base can run into the tens of percent, not a low single digit one, unless the product's own usage curve happens to avoid that window; this is easy to miss in aggregate metrics and easy to catch with a targeted test around that boundary.
• Storing only a fixed UTC offset per user (rather than a named time zone) breaks silently across a daylight saving transition, since the true local offset changes twice a year but a stored fixed offset does not.
• A single canonical reporting timezone for a global company is a legitimate, common choice, but it must be a DECISION stated to stakeholders, not a UTC default that nobody actually chose on purpose.