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

Direct answer

Compute each user's own historical mean and standard deviation of transaction amount using a window function, then flag a transaction when it exceeds mean + 3*stddev. The two judgment calls that separate a correct answer from a naive one: exclude the transaction being tested from its own baseline (otherwise a huge outlier drags its own mean and standard deviation up and can mask itself), and require a minimum history size before flagging anything, since one or two prior transactions produce a meaningless standard deviation.

Structured elaboration

• Baseline window: PARTITION BY user_id ORDER BY occurred_at RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING. The AND INTERVAL 1 DAY PRECEDING upper bound is what excludes the current transaction from its own baseline.
• Minimum-history guard: require COUNT(*) OVER (...) >= 5 (or whatever floor the business accepts) before evaluating the threshold at all. Below that, STDDEV_SAMP is either NULL (fewer than 2 rows) or wildly noisy.
• Threshold: 3 standard deviations is a common default (roughly the 99.7th percentile under a normal assumption), but transaction amounts are usually right-skewed, not normal, so this is a heuristic guardrail, not a calibrated p-value. State that assumption explicitly rather than presenting the 3-sigma cutoff as statistically rigorous.

Worked example

SELECT
  transaction_id, user_id, amount, occurred_at,
  baseline_mean, baseline_stddev, n_hist,
  (n_hist >= 5 AND baseline_stddev > 0
   AND amount > baseline_mean + 3 * baseline_stddev) AS is_anomalous
FROM (
  SELECT
    transaction_id, user_id, amount, occurred_at,
    AVG(amount) OVER w AS baseline_mean,
    STDDEV_SAMP(amount) OVER w AS baseline_stddev,
    COUNT(*) OVER w AS n_hist
  FROM transactions
  WINDOW w AS (
    PARTITION BY user_id ORDER BY occurred_at
    RANGE BETWEEN INTERVAL 365 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
  )
) t;

Run against a concrete history for one user (amounts $100, $105, $95, $102, $98, then $900):

The $900 transaction is compared against a baseline of mean $100.00 / standard deviation $3.81 built entirely from the five transactions BEFORE it, not including itself: $900 > 100 + 3(3.81) = 111.4$, so it is flagged. The first four rows are correctly never flagged even though some of them could, in isolation, look unusual, because n_hist < 5 blocks the rule from firing on a thin history.

Trade-offs & pitfalls

• Self-masking: if you instead compute the mean/stddev over a window that INCLUDES the current row, a genuine spike inflates its own baseline and can slip under the threshold, especially with a short history. This is the single most common mistake in a naive first draft of this query.
• Normality assumption: 3-sigma flags almost nothing on a heavy-tailed distribution and flags too much on a tight one; if the business needs a calibrated false-positive rate, a percentile-based threshold (e.g. "above this user's own 99th percentile") is more robust than a Gaussian-shaped rule.
• Cold-start users: the minimum-history guard means new or low-activity users are never protected by this check at all. Decide explicitly whether that gap is acceptable or needs a separate rule (e.g. compare against a global or cohort baseline until the user has enough history of their own).

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

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.

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

Join orders to refunds to get a NET revenue figure per order, aggregate to net revenue per user, then apply the same per-user rolling z-score outlier test as a basic outlier check, being careful that the outlier candidate's own value doesn't inflate the baseline used to judge it. At 100M+ rows the main additional concern is making the join and the per-user aggregation cheap rather than the statistics itself.

Structured elaboration

• Net revenue: orders.amount - COALESCE(refunds.refund_amount, 0), joined on order_id. A LEFT JOIN is required since most orders have no matching refund row.
• Per-user outlier test: AVG/STDDEV_SAMP of net amount PARTITION BY user_id, then flag net_amount > user_mean + 3*user_std.
• Scale considerations for 100M+ rows: partition the refunds join and the per-user window on user_id so the engine can compute both efficiently without an expensive full shuffle -- a shuffle is the physical movement of rows between machines in a distributed engine so that every row sharing a key (here, the same user_id) ends up on the same machine before a per-user join or aggregate can be computed at all. If the engine supports it, a hash- or range-partitioned physical layout on user_id avoids a repartition step before the window function runs -- this is a different, physical-storage sense of "partition" from the PARTITION BY user_id window-function clause used above: that clause tells the engine how to group rows for computation, while a partitioned physical layout is about how the table's data already sits on disk/across nodes before the query starts. Precompute per-user aggregates (mean, count, sum of squares for standard deviation) as an incremental, maintained table rather than recomputing the full window over the whole history on every run, if this check needs to run frequently.

Worked example

One user's orders, one of which was partially refunded:

Computed with a window that includes ALL six rows in this user's own baseline (the naive version): user_mean = 169.2, user_std = 162.4, and order 6's z-score is only $(500-169.2)/162.4 \approx 2.04$, which does not cross a 3-sigma threshold, even though $500 net is clearly the odd one out among five orders around $90-120. This is the same self-masking failure any per-entity outlier check runs into when the baseline includes the row being judged, made worse here: the raw $900 charge (before the refund landed) would have pulled the baseline up even further had it been included un-refunded, and even the refund-adjusted $500 still inflates its own mean and standard deviation enough to blunt its own z-score. The fix is identical to the basic case: compute user_mean/user_std over the user's history EXCLUDING the row being tested, so the $500 net order is judged against a clean baseline of {100, 120, 90, 110, 95} (mean 103, std about 12), giving a z-score over 30, an unambiguous flag. As full, runnable queries:

-- naive: baseline includes the row being tested
WITH net AS (
  SELECT o.order_id, o.user_id, o.order_date,
         o.amount - COALESCE(r.refund_amount, 0) AS net_amount
  FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id
)
SELECT order_id, user_id, net_amount,
  AVG(net_amount) OVER w AS naive_mean,
  STDDEV_SAMP(net_amount) OVER w AS naive_std,
  (net_amount - AVG(net_amount) OVER w) / STDDEV_SAMP(net_amount) OVER w AS naive_z
FROM net
WINDOW w AS (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

-- fixed: baseline excludes the row being tested
WITH net AS (
  SELECT o.order_id, o.user_id, o.order_date,
         o.amount - COALESCE(r.refund_amount, 0) AS net_amount
  FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id
)
SELECT order_id, user_id, order_date, net_amount,
  baseline_mean, baseline_stddev,
  (baseline_stddev > 0 AND net_amount > baseline_mean + 3 * baseline_stddev) AS is_anomalous
FROM (
  SELECT order_id, user_id, order_date, net_amount,
    AVG(net_amount) OVER w AS baseline_mean,
    STDDEV_SAMP(net_amount) OVER w AS baseline_stddev
  FROM net
  WINDOW w AS (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
) t
ORDER BY order_id;

Running the naive version against this exact six-order history reproduces naive_mean = 169.17, naive_std = 162.43, naive_z ~= 2.04 at order 6 (the self-masked, non-flagging result described above); running the fixed version reproduces baseline_mean = 103.0, baseline_stddev ~= 12.04 at order 6, with is_anomalous = true.

Trade-offs & pitfalls

• Refund timing matters: if a refund can land days or weeks after the original order, decide whether net revenue is computed as-of the order date (requiring a later correction pass when a refund arrives) or as-of a report date (simpler but revises history). State this assumption; it changes how "outlier" is even defined for an order refunded after the check already ran.
• The self-masking bug is the single most likely defect in a first-draft answer to this question: reviewers should specifically check whether the baseline window excludes the row under test.
• At true 100M+ row scale, an unindexed refunds join or a non-partitioned window is the most common performance failure; partitioning/clustering both tables on user_id (or the equivalent physical layout for the engine in use) is the first lever, before reaching for approximate methods.