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

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.

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 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

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.