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

Direct answer

Build one row per time interval (for example, week since signup) with the count of users still "at risk" of churning that interval and the count who actually churned in it, compute each interval's conditional survival probability as 1 - churn_count/at_risk_count, then take the running (cumulative) PRODUCT of those conditional probabilities across intervals, since SQL has no native window aggregate for a running product.

Structured elaboration

• At-risk count for interval $t$: users who have NOT yet churned entering interval $t$, i.e. whose last observed activity is at or after interval $t-1$ (they were still around at the start of this interval).
• Churn count for interval $t$: users whose last observed activity falls exactly at $t-1$ AND who have had enough time to be considered truly churned rather than merely not-yet-observed-again (the right-censoring guard below).
• Right-censoring: a user whose last activity is at the very edge of the observation window hasn't necessarily churned; they may simply not have had the chance to show more activity yet, because not enough time has passed since. The correct handling excludes such users from the CHURNED count for the final interval (they are still counted as at-risk, just not as an observed churn event), rather than assuming their apparent inactivity is a confirmed churn.
• Cumulative survival via log-sum-exp: multiplying a series of numbers together gives exactly the same result as adding their logarithms and then exponentiating the sum (for two numbers, $a \times b = \exp(\ln a + \ln b)$), which is why exponentiating a running SUM of logs can stand in for a running PRODUCT. Since there's no native PRODUCT window aggregate, compute it as $S(t) = \exp\left(\sum_{i \le t} \ln(1 - \text{churn}_i/\text{at\_risk}_i)\right)$, implemented as EXP(SUM(LN(1 - churned::float/at_risk)) OVER (ORDER BY t)).

Worked example

Six users, all signed up in week 0, with these last-active weeks: user A last active week 1 (churns entering week 2), user B week 2 (churns entering week 3), user C week 4 (still active at the 4-week observation boundary, right-censored, never counted as churned), user D week 0 (churns entering week 1), user E week 3 (churns entering week 4), user F week 4 (censored, same as C).

WITH intervals AS (SELECT unnest(generate_series(1, 4)) AS t),
per_interval AS (
  SELECT i.t,
    SUM(CASE WHEN u.last_active_week >= i.t - 1 THEN 1 ELSE 0 END) AS at_risk,
    SUM(CASE WHEN u.last_active_week = i.t - 1 AND u.last_active_week < 4 THEN 1 ELSE 0 END) AS churned
  FROM intervals i CROSS JOIN users u
  GROUP BY i.t
)
SELECT t, at_risk, churned,
  ROUND(1.0 - churned::float/at_risk, 4) AS conditional_survival,
  ROUND(EXP(SUM(LN(1.0 - churned::float/at_risk)) OVER (ORDER BY t)), 4) AS cumulative_survival
FROM per_interval ORDER BY t;

This produces, and was verified against the manual at-risk/churn counts for the six users above:

Users C and F, both last active at the final observation boundary (week 4), are correctly counted in at_risk at every interval up through week 4 but never counted in churned anywhere, exactly because of the u.last_active_week < 4 right-censoring guard; without that guard, a naive query would misclassify both of them as having churned in week 4 simply because week 4 happens to be both their last observed activity AND the edge of the data, which is a fundamentally different (and much stronger) claim than what the data actually supports.

Trade-offs & pitfalls

• The right-censoring guard is the single detail that separates a correct Kaplan-Meier implementation from a superficially similar but wrong one: without it, every user whose last activity coincides with "now" gets counted as churned purely because the observation window ended, which silently understates true survival, especially for the most RECENT cohorts, who have had the least time to be observed.
• This construction assumes discrete, evenly-spaced intervals (weeks); a true continuous-time Kaplan-Meier estimator handles churn events at arbitrary exact timestamps, which this simplification doesn't attempt.
• Real-world data has ties, re-activation, and multiple partial observations that complicate the at-risk/churn bookkeeping further; treat this as the clean, textbook version of the calculation and expect production data to need additional guards (deduplication, handling users who reactivate after apparently churning) layered on top.

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

At the scale of roughly a billion entities and a hundred billion events a year, the design question shifts from "how do I write the query" to "how do I avoid ever computing the full thing from scratch": partition and cluster the storage to match the query pattern, prefer incremental refresh over full recompute, and choose pre-aggregation over raw window functions wherever the business tolerance for latency allows it.

Structured elaboration

• Storage and partitioning: partition by the natural time grain the metric refreshes on (commonly daily), and cluster within each partition by the entity key most queries filter or group by, so a query touches only the relevant slice of data rather than scanning the full history.
• Compute engine choice: a distributed, columnar engine (BigQuery, Spark, Snowflake, Redshift, or similar) is the right class of tool at this scale; the specific choice usually comes down to what the organization already operates, its cost model (on-demand per-byte-scanned vs. provisioned cluster time), and how well it supports incremental/merge operations, not a single universally-correct answer.
• Incremental refresh over full recompute: maintain the metric as a table that's updated only for the partitions affected by new (or corrected) data since the last run, rather than recomputing the entire history nightly; this is the same incremental-refresh principle used for any single running total, applied here at the level of an entire cohort/activity pipeline.
• Window functions vs. pre-aggregation trade-off: a window function computed over raw, unaggregated events at this scale is expensive because it has to sort and partition a massive row set; pre-aggregating to a coarser grain (for example, one row per entity per day) BEFORE applying any window logic dramatically shrinks the data the window function actually has to process, at the cost of losing sub-day granularity.
• Cost and monitoring: track bytes scanned (or compute-hours) per run as the primary cost signal, and alert on partition-level staleness (a partition that hasn't refreshed on schedule) as the primary correctness signal, since a stale partition silently serving old data is a much more common failure mode at this scale than an outright query error.

Worked example

A concrete before/after: at a scale of 100 billion events accumulated over a year, that averages 100,000,000,000 / 365, or roughly 274 million, new events per day. Recomputing a full 100-billion-event history every night to refresh a cohort or activity metric means every run's cost and duration scales with the ENTIRE accumulated history, growing worse every single day even if the actual NEW data volume per day stays constant at that ~274 million: by the end of year one, a full nightly recompute is reading 100,000,000,000 / 274,000,000, or roughly 365 times more data, than a run that only touches that day's new events -- and that ratio keeps climbing every additional day the full-recompute design stays in place. Restructuring so each day's run only reads and reprocesses that day's new (or corrected) partition (~274 million events), merges the result into an already-maintained summary table, and never re-touches settled historical partitions, means the run's cost and duration stay roughly constant at that ~274-million-event scale as the total history grows, instead of climbing toward the full 100 billion. This is the same incremental-merge-over-full-recompute principle scaled up from a single running total to an entire pipeline.

Trade-offs & pitfalls

• Pre-aggregating too early throws away information you can't get back: if the business later needs sub-day granularity (hourly retention, for example) and the design only ever stored daily pre-aggregates, that history is gone; decide the finest grain any FUTURE analysis might plausibly need before committing to a pre-aggregation level.
• "Incremental" designs are only as correct as their handling of corrections to old data: a correction landing against an old partition still requires replaying forward from the correction point rather than just patching the current total, exactly as with any incrementally-maintained running total, at greater cost here given the larger scale of what would need to be replayed.
• Cost monitoring needs to be a first-class part of the design, not an afterthought: at this data volume, an accidental full-table scan from a poorly-partitioned query can be enormously more expensive than the routine incremental job it was meant to support.

Direct answer

DAU, WAU, and MAU are all the SAME underlying computation (distinct active users in a trailing window ending on a reference date), differing only in window length (1, 7, and 30 days respectively); computing all three efficiently for a large sliding history means avoiding a naive per-day full re-scan and instead either pre-aggregating daily distinct-user sets or using an incrementally-maintained approximate structure at very large scale.

Structured elaboration

The shared structure. Each of DAU, WAU, MAU is COUNT(DISTINCT user_id) WHERE event_date BETWEEN (ref_date - window + 1) AND ref_date, with window = 1, 7, 30. Writing all three as parallel subqueries (or CTEs) against the same base events table, sharing a single reference-date parameter, keeps the three numbers internally consistent (computed from the exact same underlying activity definition and reference point) rather than risking drift if they were computed by three separately-written queries at different times.

DAU/WAU as the "within-week" stickiness signal, distinct from DAU/MAU. DAU/WAU answers "of the people active this WEEK, how many show up on a typical single day," a tighter, faster-moving signal than DAU/MAU, useful for products with a strong weekly cadence where the interesting question is short-term consistency rather than month-scale reach.

Efficiency for distinct users over sliding windows, the real engineering question. A naive approach recomputes COUNT(DISTINCT user_id) from scratch for every reference date by re-scanning the full window's raw events, which is wasteful when adjacent reference dates share almost all of their underlying data (today's MAU window and yesterday's MAU window overlap in 29 of their 30 days). Two standard efficiency improvements: (1) PRE-AGGREGATE the base fact to one row per (user_id, activity_date) (deduplicated) once, so that all subsequent sliding-window queries at least start from a much smaller, deduplicated base rather than raw event volume; (2) for the sliding computation itself, either maintain a per-day distinct-user COUNT that can be summed incrementally is NOT valid for a distinct-count sliding window (since a user active on both an entering day and an already-counted day would be double-subtracted on removal), so exact distinct-count sliding windows generally still require a windowed re-aggregation over the deduplicated (user_id, date) base, whereas at very large scale (billions of users), an APPROXIMATE distinct-count structure (a probabilistic sketch that supports incremental merge/unmerge as the window slides) trades a small, bounded error for the ability to update the count incrementally rather than recomputing the full distinct set on every slide.

Worked example

Schema: events(user_id, event_date). Six rows across 4 users, with a duplicate row for one user to test deduplication, reference date 2026-01-31:

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events VALUES
 (1,'2026-01-31'),                                    -- active today: counts in DAU, WAU, MAU
 (2,'2026-01-28'),                                     -- active this week, not today: WAU, MAU only
 (3,'2026-01-10'),                                     -- active this month, not this week: MAU only
 (4,'2025-12-01'),                                     -- active over 30 days ago: none of the three
 (5,'2026-01-31'), (5,'2026-01-31'), (5,'2026-01-30'); -- duplicate row for u5, must not double count

WITH ref AS (SELECT date('2026-01-31') AS ref_date),
activity AS (SELECT DISTINCT user_id, date(event_date) AS d FROM events)
SELECT
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d = ref_date) AS dau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-6 days') AND ref_date) AS wau,
    (SELECT COUNT(DISTINCT user_id) FROM activity, ref WHERE d BETWEEN date(ref_date,'-29 days') AND ref_date) AS mau;

Executed against SQLite. Output:

dau | wau | mau
2   | 3   | 4

DAU/WAU $= 2/3 \approx 0.6667$, DAU/MAU $= 2/4 = 0.5$. Manual check: DAU (active exactly on 01-31) is users 1 and 5, since 5's duplicate row still counts as one user; WAU (01-25 through 01-31) adds user 2 (active 01-28), giving {1,2,5}=3; MAU (01-02 through 01-31) adds user 3 (active 01-10), giving {1,2,3,5}=4; user 4 (active 2025-12-01, more than 30 days before the reference date) never enters any of the three windows.

Complexity

Time for a single reference date: $O(n)$ over the events considered within the largest window (MAU's 30 days), since the query is effectively three overlapping distinct-count aggregations sharing the same deduplicated base. Space: $O(u)$ for the distinct users in the widest window. Computing this for EVERY day over a long history naively (re-running the full query once per day) costs $O(d \times n)$ for $d$ days of history, which is where the pre-aggregation/incremental-structure discussion above becomes a genuine, not just theoretical, efficiency concern at scale.

Edge cases

• A user active only on the FIRST day of the 30-day MAU window (day 30 back from reference): correctly included in MAU, correctly excluded from WAU and DAU, since they fall outside those narrower windows.
• All activity for a user landing on exactly the reference date, with multiple duplicate rows (as with user 5): collapses correctly to one distinct-user contribution in every window that includes that date, confirming the DISTINCT step is load-bearing, not decorative.

Trade-offs and pitfalls

• A common performance mistake is computing this per reference date by re-scanning raw (non-deduplicated) events every time, rather than building the deduplicated (user_id, date) base once; at real event volumes (many events per user per day), this can mean re-scanning an order of magnitude more rows than necessary for every single day's DAU/WAU/MAU computation.
• Naively trying to maintain an EXACT distinct count incrementally by adding the entering day and subtracting the exiting day is mathematically wrong, since a user active on BOTH the exiting day and a still-in-window day would be incorrectly removed from the running total; this is exactly why exact sliding distinct-counts either re-aggregate over the window or require a more careful structure (a per-user last-seen-date index) rather than a naive running counter.
• Reaching for an approximate (probabilistic) distinct-count structure before the exact approach has actually become a measured bottleneck is premature optimization; the deduplicated-base-plus-windowed-aggregation approach shown here comfortably handles a wide range of real-world scales, and the approximate-structure discussion is included here as the honest answer to "how would this scale further," not as a default recommendation for every retention pipeline.

Direct answer

When exact COUNT(DISTINCT) over a rolling or cumulative window is too slow or unsupported, maintain a HyperLogLog (HLL) sketch per time bucket, merge sketches to answer a query over any combination of buckets, and fall back to an exact count only for the small cohorts where the approximation error would actually matter.

Structured elaboration

• Why a rolling exact distinct count is expensive: a plain COUNT(DISTINCT) needs to see every individual row in the window to deduplicate, so a rolling version re-scans (or re-hashes) the whole window on every row unless the engine has special support for it, which most do not for arbitrary window frames.
• HyperLogLog sketches: a compact, fixed-size probabilistic data structure that estimates the number of distinct elements in a set, with a small, well-characterized relative error (commonly around 1-2% with standard parameters -- for example, a sketch that estimates 49,200 distinct users when the true count is 50,000 has a relative error of (50,000 - 49,200) / 50,000 = 1.6%, comfortably inside that range), and crucially, sketches for disjoint sets can be MERGED into a sketch for their union without re-touching the original data.
• Maintaining and merging sketches for a cumulative count: build and store one small sketch per day (or whatever the finest bucket is), then answer "distinct count over any date range" by merging the relevant days' sketches at query time, an operation that's cheap relative to re-scanning raw rows.
• Backfill approach: because sketches merge losslessly for the union operation, backfilling historical days just means building (or rebuilding) that day's sketch from that day's raw data once, then it can be merged into any future range query without ever touching that day's raw rows again.
• Producing exact counts for small cohorts: HLL's relative error is a percentage of the true count, so on a cohort of a few hundred or thousand distinct entities the absolute error is small in absolute terms but can matter for reporting; falling back to an exact COUNT(DISTINCT) on the (small, cheap-to-scan) raw rows for those specific small cohorts sidesteps the approximation entirely where its cost is negligible.

Worked example

A merge query for "unique users active over the last 7 days" from 7 daily sketches looks conceptually like:

SELECT date_bucket, HLL_ESTIMATE(HLL_UNION_AGG(daily_sketch)) AS estimated_distinct_users
FROM daily_user_sketches
WHERE date_bucket BETWEEN CURRENT_DATE - 6 AND CURRENT_DATE
GROUP BY date_bucket;

(exact function names vary by engine: BigQuery's HLL_COUNT.MERGE/HLL_COUNT.INIT, Postgres via the hll extension, Redshift's HLL type, etc. -- verify the exact syntax against the engine actually in use rather than assuming portability). The daily sketches themselves are each built once, at ingestion time or in a nightly job, from that single day's raw event rows, and never need to be rebuilt as long as that day's data doesn't change; a 7-day, 30-day, or 365-day rolling estimate is then just a merge over the corresponding set of already-built daily sketches, not a re-scan of 7, 30, or 365 days of raw data. A concrete illustration of why merging works, and why it isn't as simple as adding daily counts: suppose day 1 has 500 distinct active users and day 2 has 600, with 200 of those users active on BOTH days (the overlap). The true 2-day distinct count is 500 + 600 - 200 = 900 (basic inclusion-exclusion), not 500 + 600 = 1,100 -- naively adding the two daily counts would double-count the 200 overlapping users. An HLL UNION merge approximates that same 900, because the sketch is built to support exactly this set-union operation directly, without ever re-scanning either day's raw rows to work out who the overlapping 200 users actually are.

Trade-offs & pitfalls

• HLL trades a small, bounded relative error for large gains in speed and storage; this is the right trade for a company-wide or large-cohort dashboard number, and the wrong trade for a billing-critical or legally-audited exact count.
• Sketches must be built at a fine enough granularity to support every range you'll ever need to merge: if only monthly sketches are stored, a 7-day rolling window can't be reconstructed from them; build at the finest practically useful grain (typically daily) and merge upward.
• Union merging is what makes this cheap; INTERSECTION or set-difference operations on HLL sketches are far less accurate (or unsupported) and should not be assumed to work the same way -- verify what operations a given HLL implementation actually supports before relying on anything beyond a union/count.