skip to content

A table of 50,000 rows is grouped by one key column and the group sizes total 48,600 — where did the rest go?

level: middleimportance: must knowfreq 64%

answer

  1. the shortfall is in the key column
  2. no key value means no group to join
  3. excluded entirely, or one group of its own
  4. totals across groups need not match the table

basics

~20 s

Those 1,400 rows carry no value in the grouping key. A tool either gathers such rows into one group of their own or leaves them out of the computation entirely; here it left them out, so the group sizes no longer reconcile with the table.

solid answer

~50 s

The shortfall is in the **grouping key**, not in the values being aggregated: 1,400 rows have no key value, so there is no key for them to be collected under. What happens next is a fork, and it is one of the real disagreements between designs — some treat the absent marker as a key value in its own right and give those rows a group, others exclude them from the grouped operation altogether. Both behaviours are defaults you can usually set explicitly. The consequence holds either way and is the part worth saying: once some rows can silently leave, the group sizes and the per-group totals no longer have to add up to the whole table. So do not state one outcome as the rule — say which behaviour you want, set it, and reconcile the group sizes against the input's row count.

go deeper

for a junior

Know that a row with no value in the grouping key has no key to be filed under, and that such rows may not appear anywhere in the grouped result.

for a middle

Explain the fork — the rows either form one group of their own or leave the computation — and the consequence that holds either way: the group sizes need not add up to the input's row count.

for a senior

Show you would set the behaviour explicitly rather than inherit a default, and that you read a growing shortfall as an upstream data-quality change rather than as a bug in your own step.

for a principal

Decide what a record with no category means to the business before choosing a behaviour, and make the choice a convention across reports so two teams do not quote different totals for the same input.

## What the arithmetic is telling you The grouped operation gives every row a key, computes once per key, and reassembles the answers. If the sizes of all the groups add up to fewer rows than the input has, and no row-level condition removed anything, then some rows were never placed under a key. The overwhelmingly common reason is that **the grouping key had no value on those rows** — the column was empty, unparsed, or genuinely unknown for that record. Note precisely where the absence is. This is absence in the **key column**, the thing that decides which rows belong together. It is a different question from what a reduction does with absent entries inside the column being summed or averaged, which affects the numbers inside a group and never the number of rows the groups hold between them. Diagnosing the wrong one of the two is the classic wasted hour. ## The fork, and why you may not assume a side of it A row with no key value cannot be filed under a value, so a tool has to make a decision for you, and the designs in this space genuinely split: - **Give the absent marker a group of its own.** The rows stay in the computation and arrive as one extra group whose identity is "key not present". The sizes then reconcile with the table, and there is a visible row you can point at. - **Exclude them from the grouped operation.** Those rows take no part at all: no group, no contribution to any total, and no trace in the result that they existed. | | Absent key gets its own group | Absent key rows excluded | |---|---|---| | Group sizes add up to the table | yes | no | | The lost rows are visible in the result | yes, as one labelled group | no, nothing marks them | | A per-group share of the total | shares are of the whole table | shares are of the surviving rows only | | Risk it carries | an extra group a consumer may not expect | a silent shortfall nobody sees | Both are defensible, and in most tools this is a setting on the grouped operation rather than a fixed law. The mistake in an interview is to state one of the two flatly as *the* behaviour; the mistake in production is to inherit whichever one the default gave you without noticing which it was. ## Why a default exists at all Excluding rows with no key is not arbitrary. A key with no value is not a fact about the world — it says the record could not be classified — so gathering all such records into one bucket invents a category that the business never defined, and anybody reading the result may take that bucket for a real category. Keeping them, on the other hand, is the only way the result accounts for every input row. Neither position is free, which is exactly why tools chose different defaults and why the setting exists. ## The consequence that survives both designs This is the part that is true regardless of which tool you are holding, and it is what makes the subject interview-worthy: 1. **Totals across groups may not equal the total over the table.** A figure computed by summing the per-group results silently differs from the same figure computed over the input. 2. **Every per-group share is computed against the wrong base** when rows have left. A group holding 5,000 of 48,600 surviving rows looks larger than it is against the real 50,000. 3. **The shortfall grows with data quality, not with volume.** It is stable while the upstream source behaves and jumps the week the source starts emitting records with no category — which is precisely the week the report is trusted and wrong. 4. **Nothing raises.** No error, no warning, no marker in the result. The only signal is the arithmetic in the question: 48,600 against 50,000. ## What a strong answer does next First, name the cause in one sentence and locate it in the key rather than in the values. Second, state the fork rather than one branch, and say that you would set the behaviour explicitly instead of relying on a default that differs between tools and between versions. Third, decide what those rows *mean* before deciding what to do with them: if a record with no category is a data-quality event, you want it visible as its own group so somebody has to look at it; if it is genuinely out of scope, excluding it is correct, but then the caption on the report should say so, because a reader comparing your total against the source count will otherwise find a discrepancy you already knew about.

  • Why is this different from an absent value in the column being summed?
    An absent value in the summed column changes the number a group reports; the rows are still in a group and still counted in the group sizes. An absent value in the grouping key changes which rows take part at all, so it moves the result's row count and can make the per-group sizes fall short of the input.
  • If rows with no key do get their own group, what should you watch for downstream?
    An extra group appears that no reference list contains, and a consumer expecting only real categories may treat it as one. Its identity is also awkward to match against anything else, because "no value" is not a value. Label it plainly and decide whether it belongs in the shipped result or in a separate exception count.
  • The shortfall was zero for months and is 1,400 today. What does that suggest?
    The behaviour did not change; the input did. Rows with no key value started arriving, most often because an upstream producer began emitting the field empty, a join upstream failed to populate it, or a parse now yields nothing for a format it used to handle. The grouped operation is reporting a data-quality change, not causing one.

saying these in an interview costs you the question

  • Saying rows with an absent key are always dropped from the grouping
  • Saying they always form their own group labelled as absent
  • Blaming the aggregate function on the column being summed
  • Assuming per-group shares are still shares of the whole table
  • Expecting a warning or an error to announce the shortfall