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

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.

Direct answer

Compute each segment's absolute change and its share of the TOTAL change between the two periods, not just its own percentage growth, then rank by absolute contribution. A segment can grow 50% and still be a rounding error to the total, while a segment that shrank 5% in a huge base can be the entire story.

Structured elaboration

• Absolute delta per segment: curr_value - prev_value, the raw amount this segment added or subtracted.
• Share of total change: divide each segment's own delta by the ABSOLUTE VALUE of the sum of all deltas across segments (that sum equals the total metric's own period-over-period change; taking the absolute value keeps the denominator positive so each segment's own delta keeps its own sign -- see the worked example below for what dividing by the signed total instead would do to every row's sign). This is the number that actually answers "who drove the change", because it accounts for both direction and magnitude relative to everyone else's movement, including segments that moved in the opposite direction.
• The normalization caveat: if the overall population of segments (or the population within a segment) changed size between the two periods, a segment's delta partly reflects that size change, not a genuine per-unit shift. Before concluding a segment is a real "driver", check whether its underlying denominator (user count, order count, whatever the segment is measured over) moved too; if it did, a per-unit or rate-based comparison is more honest than a raw-value comparison.

Worked example

WITH deltas AS (
  SELECT segment, prev_value, curr_value, (curr_value - prev_value) AS delta
  FROM segment_metric
),
totals AS (
  SELECT SUM(delta) AS total_delta FROM deltas
)
SELECT d.segment, d.prev_value, d.curr_value, d.delta,
  ROUND(100.0 * d.delta / ABS(t.total_delta), 1) AS pct_of_total_change
FROM deltas d CROSS JOIN totals t
ORDER BY ABS(d.delta) DESC
LIMIT 3;

Four regions, revenue before and after:

Total change across all four segments is $9000+2600+1450+2500 - (10000+3000+1500+2000) = 15550 - 16500 = -950$. Each row's "% of total change" divides that segment's own delta by the ABSOLUTE size of the total change -- 950, the magnitude of -950 -- not by the signed -950 value itself. Dividing by the signed total would flip every segment's sign (DE's +500 would read as -52.6% instead of the +52.6% shown above), which is backwards for a table whose whole point is showing who grew versus who shrank. Concretely: DE = 500 / 950 = +52.6%; FR = -50 / 950 = -5.3%; UK = -400 / 950 = -42.1%; US = -1000 / 950 = -105.3% -- because the denominator is always positive, each row keeps its own delta's sign. Reading this table: US alone accounts for slightly MORE than 100% of the total decline (-105.3%), because DE's growth (+52.6% of the total swing, in the opposite direction) is partially offsetting US's drop. The naive question "what drove the decline" answered by looking only at which segment shrank the MOST in percentage terms would have picked DE's neighbors or overlooked how much DE's own growth is masking the US problem; the delta-share table makes that masking visible. The query above already does the ranking in SQL: ordering by ABS(delta) and limiting to 3 returns US, DE, and UK in that order and drops FR, which is exactly the "top 3 segments that drove it" the question asks for, not something read off the table by eye.

Trade-offs & pitfalls

• Percentages of total change can exceed 100% or go negative, and that is correct, not a bug: it happens whenever some segments move in the opposite direction from the overall trend, exactly as shown above with DE.
• A shrinking segment isn't automatically "the driver" if its own population also shrank; always sanity-check the denominator before attributing a value change entirely to behavior change.
• This decomposition is purely arithmetic, not causal: it tells you WHERE the change is concentrated, not WHY it happened. Follow it with a segment-specific investigation, not a conclusion.

Direct answer

Compute churn_date by looking for the FIRST point in each user's own chronological activity history where a gap of 30 or more consecutive days opens up between two events (using LAG() to compare each event against its predecessor), falling back to the OPEN-ENDED gap between the user's most recent activity and the analysis date if no such internal gap exists; separately, "currently active" is a different, simpler question, whether the user's most recent activity is within 30 days of the analysis date, and the two are not mutually exclusive: a user can have a churn_date from a past silence AND be currently active again if they later returned.

Structured elaboration

Two genuinely different questions, both worth answering. "When did this user first go 30 days silent" (churn_date, a historical fact that, once true, stays true even if the user later returns) and "is this user active right now" (a point-in-time status as of analysis_date) are not the same question, and the question's own second requirement, "show how to report users currently active," only makes sense as a SEPARATE check precisely because a user can have both a churn_date in their past AND be currently active again after reactivating.

Finding the first qualifying gap. Using LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) to pair each event with its immediately preceding event for the same user, then computing the day-gap between each consecutive pair; the FIRST (chronologically earliest) pair whose gap reaches 30+ days is what determines churn_date, set to 30 days after the gap's starting event (the moment the 30-day silence threshold was actually crossed).

The open-ended gap, a special case that needs its own check. A user whose LAST activity is followed by nothing (no later event at all) has no "next event" for LAG to compare against in the forward direction, so their potential churn from silence-since-last-activity has to be checked separately, comparing their MAX(occurred_at) against analysis_date itself, with churn_date = last_active + 30 days if that gap also reaches 30+.

Currently active, the second required output. days_since_last_active < 30 as of analysis_date, computed independently of whether a churn_date exists at all; this correctly lets a reactivated user (one who has a churn_date from an earlier silence but has since returned) show up as BOTH "has a churn_date" and "currently active," rather than forcing a single binary status that would lose that real nuance.

Worked example

Schema: user_events(user_id, occurred_at). Four users engineered to test the distinct scenarios: clean open-ended churn, a past-gap-then-reactivation, a user who never has a qualifying gap at all, and the exact-30-day boundary. Analysis date pinned to 2026-02-15:

CREATE TABLE user_events (user_id INTEGER, occurred_at TEXT);
INSERT INTO user_events VALUES
 (1,'2025-10-25'),(1,'2025-11-01'),                                                    -- u1: goes silent for good after Nov 1
 (2,'2025-11-01'),(2,'2026-02-10'),                                                     -- u2: one 101-day gap, then returns 5 days before analysis_date
 (3,'2025-10-01'),(3,'2025-10-25'),(3,'2025-11-18'),(3,'2025-12-12'),(3,'2026-01-05'),(3,'2026-01-29'), -- u3: every gap under 30 days
 (4,'2026-01-01'),(4,'2026-01-16');                                                     -- u4: last active exactly 30 days before analysis_date

WITH params AS (SELECT date('2026-02-15') AS analysis_date),
ordered AS (
    SELECT user_id, occurred_at,
        LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_occurred_at
    FROM user_events
),
gaps AS (
    SELECT user_id, prev_occurred_at, occurred_at,
        CAST(julianday(occurred_at) - julianday(prev_occurred_at) AS INTEGER) AS gap_days
    FROM ordered WHERE prev_occurred_at IS NOT NULL
),
first_qualifying_gap AS (
    SELECT user_id, MIN(prev_occurred_at) AS gap_start FROM gaps WHERE gap_days >= 30 GROUP BY user_id
),
last_activity_overall AS (SELECT user_id, MAX(occurred_at) AS last_active FROM user_events GROUP BY user_id),
open_ended_gap AS (
    SELECT la.user_id, la.last_active,
        CAST(julianday((SELECT analysis_date FROM params)) - julianday(la.last_active) AS INTEGER) AS days_since_last_active
    FROM last_activity_overall la
)
SELECT oe.user_id, oe.last_active, oe.days_since_last_active,
    CASE
        WHEN fqg.gap_start IS NOT NULL THEN date(fqg.gap_start, '+30 days')
        WHEN oe.days_since_last_active >= 30 THEN date(oe.last_active, '+30 days')
        ELSE NULL
    END AS churn_date,
    CASE WHEN oe.days_since_last_active < 30 THEN 1 ELSE 0 END AS currently_active
FROM open_ended_gap oe LEFT JOIN first_qualifying_gap fqg ON fqg.user_id = oe.user_id
ORDER BY oe.user_id;

Executed against SQLite. Output:

user_id | last_active | days_since_last_active | churn_date  | currently_active
1       | 2025-11-01  | 106                     | 2025-12-01  | 0
2       | 2026-02-10  | 5                       | 2025-12-01  | 1
3       | 2026-01-29  | 17                      | NULL        | 1
4       | 2026-01-16  | 30                      | 2026-02-15  | 0

u1: no internal 30+ day gap (only gap is 7 days), so churn_date comes from the open-ended check (Nov 1 + 30 = Dec 1), currently NOT active (106 days silent). u2: the internal gap between Nov 1 and Feb 10 is 101 days, which IS a qualifying internal gap, so churn_date = Dec 1 (30 days after Nov 1), but the user is currently active (returned Feb 10, only 5 days before analysis_date), demonstrating the exact "has a churn_date AND currently active" case the direct answer names. u3: every consecutive gap stays under 30 days, so no churn_date at all (NULL, correctly, not a fabricated date), currently active. u4: last active exactly 30 days before analysis_date, the boundary case, correctly treated as churned under a >=30 convention.

Complexity

Time: $O(n \log n)$ per user for the LAG window function's implicit sort by occurred_at (or $O(n)$ if the events are already stored sorted), where $n$ is that user's event count; overall $O(N \log N)$ across all users' events combined in the worst case. Space: $O(N)$ for the windowed intermediate results.

Edge cases

• A user with exactly ONE event ever: no prev_occurred_at exists, so no internal gap can be evaluated; their churn status depends entirely on the open-ended check against analysis_date.
• A user whose first-ever event is itself more than 30 days before analysis_date but who has since been consistently active: correctly NOT churned, since the relevant gap is only ever measured between CONSECUTIVE activity, never from account creation itself.

Trade-offs and pitfalls

• Computing churn_date only from the open-ended (last-activity-to-today) gap, and ignoring internal gaps entirely, would have missed u2's real historical churn event, understating how many users have EVER gone through a churn-and-reactivation cycle, a genuinely different population from users who have simply always been steadily active.
• Reporting "currently active" as though it contradicts having a churn_date is a common misreading of this two-part design; the two fields answer different questions and u2 in the worked example is the direct proof that both can legitimately be true at once.
• The >= 30 boundary convention (u4) is a choice, not a law of nature; a team could reasonably define churn as > 30 instead, and whichever convention is chosen should be stated explicitly, since it shifts exactly-boundary users like u4 in or out of the churned population.

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

Generate the full, gap-free calendar range explicitly with a date-series function, LEFT JOIN the real data onto it, and then decide per-column whether a missing day should be treated as zero (for a count/sum-type metric) or carried forward from the last known value (for a state-type metric, like an account balance or an inventory level). Silently letting rolling/cumulative window functions run only over the rows that happen to exist is the single most common way these metrics get quietly wrong.

Structured elaboration

• Generate the complete date series: a generate_series-style function (or the engine's equivalent) producing one row per calendar day in the reporting range, independent of whether any data exists for that day.
• Left join real data onto that series: any day with no matching row in the source data becomes NULL after the join, not simply absent from the result.
• Choose the right NULL-handling per metric type: a count or sum-type metric (daily active users, daily revenue) should usually treat a missing day as 0, since "no data" here means "nothing happened", not "unknown". A state-type metric (an account's current tier, a subscription's current plan, a sensor's last reading) should usually carry the LAST KNOWN value forward, since the state didn't reset just because nothing was recorded that day.
• Carry-forward with a window function: LAST_VALUE(value IGNORE NULLS) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) fills each gap with the most recent non-null value at or before that day, which is exactly the "carry forward" semantics for a state-type metric.

Worked example

A sparse source table has values on Jan 1, Jan 2, and Jan 5 only (Jan 3-4 have no rows at all):

WITH calendar AS (
  SELECT day FROM generate_series(DATE '2025-01-01', DATE '2025-01-06', INTERVAL '1 day') AS day
),
joined AS (
  SELECT c.day, d.value FROM calendar c LEFT JOIN daily_sparse d USING (day)
)
SELECT day, value,
  LAST_VALUE(value IGNORE NULLS) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS carried_forward
FROM joined ORDER BY day;

Result:

Without the generate_series step, Jan 3, 4, and 6 would not appear as rows at all, and a rolling average computed only over the three rows that DO exist (Jan 1, 2, 5) would silently treat Jan 2 and Jan 5 as adjacent days, understating the true gap between them. With it, the gap is visible and each metric type is filled correctly for its own semantics.

Trade-offs & pitfalls

• Zero-fill and carry-forward are not interchangeable, and picking the wrong one is a real, easy-to-make error: zero-filling a state metric makes it look like the account balance vanished; carrying forward a count metric makes a genuinely quiet day look like a repeat of the last active one.
• LAST_VALUE ... IGNORE NULLS syntax varies by engine (some require RESPECT NULLS/IGNORE NULLS as shown, others need a different construct such as a manual MAX over a group formed by counting non-null values); verify the exact syntax against the engine actually in use rather than assuming portability.
• A long unbroken run of missing days carried forward silently hides a real outage; pair carry-forward filling with a separate "days since last real observation" column so a legitimate multi-week data gap doesn't get invisibly smoothed over.