Why can't daily distinct-user counts be summed, and what should a rollup table store instead?

## Why counts of distinct things are not additive An aggregate is additive when the result over a union of disjoint sets is a function of the results over the parts. `SUM(revenue)` qualifies: revenue booked on Monday and revenue booked on Tuesday are different money, and the two-day total is the sum. `COUNT(DISTINCT user_id)` does not, because the *rows* are disjoint but the *values* are not. A user who visits on both days contributes to both daily counts and must contribute once to the two-day count. So from `dau(Mon) = 100000` and `dau(Tue) = 120000` all you can derive is a range: at least 120,000 (if Monday's users are a subset of Tuesday's) and at most 220,000 (if the sets are disjoint). The true value depends on overlap that the daily numbers do not record. ## The workarounds people reach for, and why each fails **Sum the days.** Overcounts, and the error grows with the window: a 30-day sum can be several times the true monthly figure for a product with loyal users. **Take the maximum day.** Undercounts, and it is insensitive to exactly the thing a monthly metric is meant to capture — reach beyond the daily regulars. **Apply a measured overlap ratio.** Works until the mix shifts. A marketing push, a seasonal pattern or a new market changes the ratio, and the metric drifts without anyone noticing because there is no signal that it is wrong. **Recompute from raw events for every window.** Correct, but it defeats the rollup: every monthly, quarterly and rolling-28-day number re-reads the fact table, which is the cost the rollup existed to remove. ## Store the state, not the answer The general principle is to store an aggregate's **intermediate, mergeable state** in the rollup instead of its final value. For averages you store sum and count; for distinct counts you store the sketch. HyperLogLog is the natural fit because its merge operation is a per-register maximum, which makes the union of two sketches identical to the sketch you would have built from the union of the two inputs. That is stronger than "approximately combinable": the merge introduces **no additional error**. A thirty-day estimate carries the same relative error as a one-day estimate — roughly `1.04/sqrt(m)` — rather than accumulating error per day the way a chain of approximations would. Operationally, engines expose three functions: one that aggregates rows into a sketch, one that merges sketches into a sketch, and one that extracts a cardinality estimate from a sketch. Names vary between products, and sketch binary formats are engine-specific, but the shape is the same everywhere. ## What the rollup looks like The rollup carries the sketch as a binary column alongside the ordinary additive measures: ```sql -- one row per day per dimension, sketch stored as a blob column SELECT event_date, country, platform, BUILD_SKETCH(user_id) AS users_sketch, -- names vary by engine COUNT(*) AS events, SUM(revenue) AS revenue FROM events GROUP BY event_date, country, platform; ``` Any window, and any subset of dimensions, is then one merge away — including ad-hoc windows nobody declared in advance, such as a rolling 28 days ending on an arbitrary date. ## Rules that make it work **Uniform parameters.** Sketches only merge if they were built with the same precision and the same hash. Changing precision mid-history creates two incompatible generations; if you must change it, rebuild the whole history or accept that the boundary is unmergeable. **Grain discipline.** The sketch is stored per rollup row, so its total storage cost is (number of rollup rows) × (sketch size). A daily rollup at a fine grain — day × country × platform × campaign — can hold more sketch bytes than the fact table holds data. Choose the grain by what will actually be queried, and consider a coarser sketch precision at fine grains where each group's cardinality is small anyway. **Consistency at the edges.** The exact daily number and the sketch-derived daily number will differ slightly. Publish one of them, not both, or consumers will file a bug. ## What sketch rollups cannot do They cannot answer set **intersection** questions well. "Users active on both Monday and Tuesday" via inclusion–exclusion subtracts two large estimates, and the errors do not cancel — the result can be badly wrong or even negative. Sketch families designed for set operations (theta or KMV-style sketches) handle this better; plain HyperLogLog does not. They also cannot be drilled into. A sketch is not a list of ids, so "which users" has no answer from the rollup, and no retention or cohort analysis can be reconstructed from it. Keep a path back to the underlying facts for those questions.