skip to content

Why can a rollup table answer SUM queries directly but not AVG or COUNT(DISTINCT user_id)?

level: middleimportance: must knowfreq 58%

answer

  1. can group results be combined again?
  2. mean of means ignores group sizes
  3. store the numerator and the denominator
  4. the same user appears on many days
  5. holistic measures need identities, not scalars

basics

~20 s

SUM, COUNT, MIN and MAX re-aggregate correctly because combining group results is the same operation again. AVG is a ratio, so store SUM and COUNT and divide at read time. Distinct counts and medians cannot be recombined at all from stored group results.

solid answer

~50 s

A measure is usable in a rollup only if it is **re-aggregatable**: applying the same function to stored group results gives the same answer as running it on the raw rows. `SUM`, `COUNT`, `MIN` and `MAX` satisfy that — summing daily sums gives the monthly sum. `AVG` does not, because averaging daily averages weights each day equally regardless of how many rows it contained; the fix is to store `sum_amount` and `row_count` and compute `sum(sum_amount) / sum(row_count)` at read time. `COUNT(DISTINCT user_id)`, medians and percentiles are **holistic**: a user active on ten days contributes to ten daily rows, so adding them double-counts, and no scalar stored per group can repair that. Your options are to store the distinct count only at the exact grain queried, keep a mergeable sketch instead of a scalar, or fall back to the fact table.

code

sql · 10 lines
sql
-- Refresh: store components, never the ratio
CREATE TABLE agg_orders_daily AS
SELECT CAST(order_ts AS DATE) AS order_day,
       country,
       sum(amount) AS sum_amount,
       count(*)    AS row_count,
       min(amount) AS min_amount,
       max(amount) AS max_amount
FROM fact_orders
GROUP BY 1, 2;

go deeper

for a junior

Recall that summing daily sums is valid but averaging daily averages is not, and that a rollup should store sums and counts rather than averages.

for a middle

Explain decomposability: which functions combine with themselves, why a ratio must be stored as numerator and denominator, and why distinct counts and medians cannot be recombined from a stored scalar at all.

for a senior

Show the production consequences — silent wrong numbers on a dashboard — and the mitigations you would pick: fixing the grain, keeping mergeable state and accepting error, or routing exact queries back to the fact table.

for a principal

Own the metric contract: which measures may be pre-aggregated, which are exact versus approximate, and how the read-side expressions are published so every consumer computes the same number.

## Re-aggregatability is the whole rule A rollup stores one row per group. Any query that reads it at a coarser grain must combine several stored rows into one answer. A measure is safe to store only when that combination is possible from the stored value alone. The formal property is that the aggregate function is **decomposable**: there exists a combine function such that combining partial results equals computing the function over the union of the inputs. This splits measures into three families. ## Additive measures: store them directly `SUM`, `COUNT`, `MIN` and `MAX` are decomposable with themselves as the combine function. Summing daily sums gives the monthly sum; taking the max of daily maxima gives the monthly max. These need no special handling, which is why almost every rollup is built out of sums and counts. A subtlety: `COUNT(*)` in the rollup must be summed, not counted, when you roll up further. Reading it as `count(*)` over the aggregate returns the number of aggregate rows, not the number of fact rows — a common and silent error. ## Ratios and derived measures: store the components `AVG` is not decomposable, because the mean of means is not the mean unless the groups are equal sized. If Monday has 1,000 orders averaging 10 and Tuesday has 10 orders averaging 500, the true two-day average is roughly 14.85, while averaging the two daily averages gives 255. The fix is standard and expected in interviews: store the numerator and the denominator separately. ```sql -- refresh SELECT day, country, sum(amount) AS sum_amount, count(*) AS row_count FROM fact_orders GROUP BY 1, 2; -- read at month grain SELECT date_trunc('month', day) AS month, sum(sum_amount) / sum(row_count) AS avg_amount FROM agg_orders_daily GROUP BY 1; ``` The same trick generalises to any ratio metric — conversion rate, average order value, error rate: store both sides of the fraction, never the quotient. Publish the read-side expression as a view so that consumers cannot accidentally average the ratio. ## Holistic measures: not recoverable from a scalar `COUNT(DISTINCT x)`, medians, percentiles and "number of users who did at least three things" are **holistic**: the answer at a coarse grain depends on identities that the group-level scalar has thrown away. A user active on Monday and Tuesday appears in both daily rows, so adding daily distinct counts double-counts them. Taking the max under-counts. There is no arithmetic fix, because the stored number does not record *which* users they were. The realistic options are: 1. **Store the distinct count only at the grain it will be queried at.** A rollup keyed by month can store monthly distinct users correctly — it just cannot be rolled up to a quarter. 2. **Store a mergeable sketch instead of a scalar.** Distinct-count and quantile sketches can be combined across groups; the price is approximation with a bounded error, and larger rows. 3. **Store the identities.** A per-day, per-user grain table is exact and rolls up correctly, but it is close to fact-table size, so it defeats the point unless the entity cardinality is small. 4. **Fall back to the fact table** for the handful of queries that need exactness, and let the rollup serve the rest. ## Semi-additive measures A third case trips people up: measures that are additive over some dimensions but not over time. An account balance or an inventory level can be summed across accounts or warehouses, but summing yesterday's and today's balance is meaningless — the correct time-wise combination is usually last-value or an average. Store them, but document the allowed aggregation per dimension, because the engine will happily sum them for you. ## What an interviewer is listening for The sequence to say out loud is: is the measure decomposable; if it is a ratio, store the components; if it is holistic, either fix the grain, keep mergeable state and accept approximation, or read the base table. Candidates who answer "we store AVG and average it later" have just described the classic reporting bug, and it is the reason this question is asked so often.

  • What is a semi-additive measure and how do you store one in a rollup?
    A measure additive over some dimensions but not over time — account balances, inventory levels, headcount. Summing across accounts is valid; summing yesterday's and today's balance is not. Store the raw value at a fixed time grain and document the allowed combination per dimension, typically last-value or average over time and sum across the others. Expose it through a view so consumers cannot sum it along the forbidden axis.
  • If you store COUNT(*) in a daily rollup, what goes wrong when someone queries it at month grain?
    They often write count(*) over the aggregate, which returns the number of aggregate rows — roughly the number of days times key combinations — rather than the number of fact rows. The stored count must be summed, not counted. Naming the column something explicit such as row_count, and serving the rollup through a view that already sums it, prevents the mistake.
  • How do you keep a distinct count usable across multiple grains without storing identities?
    Store a mergeable sketch — a fixed-size probabilistic summary of the set — rather than a scalar. Sketches from several groups can be combined, so a monthly figure derives from daily ones. The trade-off is that the answer is approximate within a bounded error and the rows are larger. If the number is used for billing or compliance, that trade is usually unacceptable.

You can add up subtotals of money without looking at the receipts, but you cannot add up counts of distinct customers without knowing whether the same person appears on two receipts.

saying these in an interview costs you the question

  • Stores AVG in the rollup and averages the averages later
  • Sums per-day distinct user counts to get a monthly figure
  • Runs count(*) over the aggregate instead of summing its stored count
  • Claims any aggregate function can be re-aggregated with enough SQL
  • Sums an account balance across days without noticing it is semi-additive

context

resolving links…