Two reports disagree about the same number

Can I prove which of the two numbers is the wrong one?

The ticket

CUSTOMER TICKET: two reports disagree and I do not know which to trust

Account: internal, raised by the sales operations team Impact: account planning is blocked Started: this morning

The active accounts report says we have 108 enterprise accounts. The plan mix report says 12. Both of these are supposed to be counting our customers and they cannot both be right. I would assume the bigger one is picking up something extra, but I genuinely do not know which one to believe, and I need a number I can put in front of the leadership team on Thursday.

Your job

1. Establish the true number independently before trusting either report.
2. Prove what the wrong one is actually counting.
3. Correct it so it answers the question its title claims.

Working notes

The support database is on 127.0.0.1:5434, database support_lab, user support. The report lives in labs/sql/_stack/query.sql. Edit it, then run tse check, which executes exactly that file. To explore interactively:

docker compose -f labs/sql/_stack/compose.yaml exec -e PGPASSWORD=demo-password \
  postgres psql -U support -d support_lab

Track

SQL and PostgreSQL

Time

about 30 minutes

Difficulty

Involved

Tier

Core

Do these first: A report is quietly missing customers

Start it

In a Codespace or a local clone:

tse start sql/02-two-reports-disagree

That provisions the broken system and prints the ticket above. Investigate with ordinary tools, then run tse check.

Look at the evidence

Real output, captured by running these commands against the broken system and checked against it on every build. It shows you what the evidence looks like. It cannot fix anything, and it will not tell you what is wrong.

Type a command you would reach for, or help.

CommandRun

Enter runs it. Shift and Enter start a new line. The up and down arrows walk back through what you have typed.

Show which commands are recorded (this is close to a hint)

Investigation scratchpad

Saved in this browser as you type. Nothing is uploaded. 0 of 7 filled in.

1. Customer symptom

In their words, not yours. Include scope and urgency.

2. Prediction

Before running anything: target layer, expected output, two likely causes.

3. Evidence gathered

The command or query, and why it is safe to run here.

4. Confirmed / disproven / still unknown

Three separate lists. This is the step people skip.

5. What it proves

One proof sentence, one safe next step, one alternate hypothesis.

6. Customer update and escalation note

Plain language. Impact first. No blame, no speculation.

7. Recall

One gap, one command to repeat tomorrow, one confidence score.

Copy as markdownClear

Hints

Each hint gives away a little more. Try to spend a few minutes on your own evidence first, because the recall is what makes it stick.

Hint 1 of 3

Do not start by picking a side. The customer has already framed this as "probably the big one is picking up something extra", and that framing is a guess you should test rather than inherit.

Establish the real number independently, from the customers table alone, with no joins involved. Now you have a fact, and both reports can be measured against it rather than against each other.

Once you have that, notice which report is wrong and by how much. A number that is too high by a random amount is one kind of problem. A number that is too high by a clean multiple, or by an amount that varies by group in a way that tracks something real, is a very different one.

Hint 2 of 3

Twelve is right. One hundred and eight is the inflated one, and the shape of the inflation is the clue: enterprise is off by roughly nine times, and the smaller plans by roughly two.

Ask what there are nine of per enterprise account, and two of per smaller one.

This is what happens when a query walks from one table to another where the relationship is one-to-many. Every customer row becomes as many rows as it has matches on the far side, and it becomes those rows before any aggregate runs. By the time the count happens, the thing being counted is no longer customers.

Run the query without its GROUP BY and look at the raw rows. What you see is what the count is counting.

Hint 3 of 3

Look at what the joins actually produce:

SELECT c.id, c.name, u.email
FROM customers c
JOIN workspaces w ON w.customer_id = c.id
JOIN users u ON u.workspace_id = w.id
ORDER BY c.id
LIMIT 15;

The same customer appears once per user. COUNT(*) over that is a user count wearing a customer label.

The joins are not the mistake, though. The report is "customers with at least one user", and establishing that genuinely needs the join. What is wrong is what gets counted afterwards:

SELECT c.plan, COUNT(DISTINCT c.id) AS customer_count
FROM customers c
JOIN workspaces w ON w.customer_id = c.id
JOIN users u ON u.workspace_id = w.id
GROUP BY c.plan
ORDER BY c.plan;

Note this correctly reports 6 starter accounts rather than 12: the other six have no users yet, and this report is deliberately about the ones that do.

Solution

Write your customer update before you read this. Comparing your wording against the model answer is worth more than reading it cold.

Reveal the solution

Solution: two reports disagreeing

What the evidence proved

Query	What it proved	What it did not prove
SELECT count(*) FROM customers WHERE plan='enterprise'	12 is the true figure	Which report is wrong, until compared
The active accounts report	Returns 108, so it is the inflated one	What it is counting instead
The same joins without GROUP BY	Each customer appears once per user
Users per workspace	Nine for enterprise, two for the rest	Which matches the 9x and 2x inflation exactly

The customer's instinct that the larger number was "picking up something extra" happened to be right, but it was a guess. Verifying independently is what turns it into a diagnosis, and roughly half the time in this situation the smaller number is the wrong one instead.

Root cause

The active accounts report joins customers to workspaces to users. That relationship is one-to-many, so each customer row is multiplied into one row per user before any aggregate runs. COUNT(*) then counts result rows, which are users, while the report's title claims they are customers.

The inflation factor is not random, and that is what makes it identifiable: enterprise accounts have nine users each and were inflated roughly ninefold, the smaller plans have two and were inflated roughly twofold.

Scoped fix

SELECT c.plan, COUNT(DISTINCT c.id) AS customer_count
FROM customers c
JOIN workspaces w ON w.customer_id = c.id
JOIN users u ON u.workspace_id = w.id
GROUP BY c.plan
ORDER BY c.plan;

The joins stay. The report is "customers with at least one user", and establishing that genuinely requires walking to the users table. What changes is what gets counted once you are there.

Note the corrected report returns 6 starter accounts, not 12. That is correct and is not the previous ticket's bug: the other six starter accounts have no users, and this report is deliberately about the ones that do. Two reports can legitimately disagree when they are asking different questions, which is exactly why naming the question precisely matters.

Customer update

The figure you can use is 12. I verified it against the account records directly rather than trusting either report.

The active accounts report was counting user records rather than accounts. It walks from accounts through to users to establish which accounts have at least one user, and in doing so it produces one row per user, so a twelve-account plan with nine users each was reported as 108. The plan mix report was never affected because it does not walk that far.

The report now counts accounts and returns 12. One thing to note: it shows 6 starter accounts rather than 12, and that is correct, because six starter accounts have no users yet and this report is specifically about accounts that do. If you need the total including those, that is the plan mix report.

Engineering escalation, if you needed one

Not an incident. Worth raising with whoever owns the reporting suite that the two reports have similar titles and answer materially different questions, which is what let this sit unnoticed.

Check your understanding

Three questions on what the evidence here proved, and what it pointedly did not. Wrong answers explain themselves, and so do right ones.

tse quiz

Check your understanding

Three questions on what the evidence proved and what it did not. Every answer explains itself, including the right one.

Question 1 of 3The customer guessed that the larger figure was picking up something extra, and they turned out to be right. Why verify independently anyway?

• Because customers are generally unreliable about their own data.
• Because the verification is cheap to run.
• Because about half the time in this situation it is the smaller figure that is wrong, and a guess that happens to land is still a guess.
• Because the customer may have misread the report.

Question 2 of 3One group came back about nine times too high and the rest about twice too high. What does that tell you?

• The multiplier matches how many users each account has, which names the mechanism instead of restating the symptom.
• The larger accounts are queried more frequently.
• The data for larger accounts is duplicated in storage.
• Two separate faults are in play, one per group.

Question 3 of 3After the joins have been applied, what is the report actually counting?

• Accounts, but only those with at least one user attached.
• Workspaces, since that is the table in the middle.
• Accounts, with some counted twice because of a data problem.
• Result rows, which after the expansion are users, while the column heading still claims they are accounts.

3 questions, none answered yet.

Why this one exists

Joining through a one-to-many relationship multiplies rows before the aggregate ever runs, so COUNT(*) counts result rows rather than the thing you named in the report.

In an interview

The instinct is to assume the smaller number is missing data. Half the time the larger one is inflated instead, and proving which before touching either is the whole skill. Interviewers watch for whether you verify the total independently rather than trusting either report.

Commands introduced

• COUNT(DISTINCT ...)
• inspecting join output before aggregating

Evidence layers

• row counts before and after aggregation
• join multiplicity
• independent verification of a total