A report is quietly missing customers

Can I prove the report is asking for what the customer thinks it asks for?

The ticket

CUSTOMER TICKET: our plan mix report is undercounting

Account: internal, raised by the finance team Impact: monthly board reporting is wrong Started: noticed during this month's close

The plan mix report says we have 34 accounts. We have 40. I checked the account list by hand twice. The enterprise and growth numbers look right to me, it seems to be the smaller plans that are off, which makes even less sense. Nothing about this report changed as far as I know.

Your job

1. Establish the true numbers independently before you read the report.
2. Prove where the rows are being lost.
3. Correct the report so it answers the question that was asked.

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

Straightforward

Tier

Core

Start it

In a Codespace or a local clone:

tse start sql/01-report-is-missing-customers

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

Before you read a single line of the report, get the true number yourself. If you start from the report you will spend the next twenty minutes explaining why it is correct.

Count the customers directly. Now you have a fact rather than two competing claims, and the gap has a size.

The customer gave you something better than the total, though, and it is worth sitting with: the larger plans look right and the smaller ones do not. A report that were simply broken would be wrong everywhere. One that is wrong in a pattern is losing a specific group of rows, and the shared property of that group is the answer.

So the question is not "why is the number wrong". It is: what do the missing accounts have in common?

Hint 2 of 3

Six accounts are missing, all on smaller plans. Find them: list the customers that do not appear in the report's result, and look at what is different about them compared to the ones that do.

The thing to internalise here is that the report reads from more than one table. Whenever a query pulls two tables together, it has to decide what to do with a row on one side that has no partner on the other, and the default is to throw it away silently. No error, no warning, no note in the output.

That default is why this class of bug survives review: the SQL is valid, it runs, and it returns a plausible answer.

Check whether every customer actually has a matching row in the other table.

Hint 3 of 3

Find the missing accounts:

SELECT c.id, c.name, c.plan
FROM customers c
LEFT JOIN workspaces w ON w.customer_id = c.id
WHERE w.id IS NULL;

Six customers, all newer, none of which has created a workspace yet. The report joins workspaces and so drops every one of them.

The report does not need a workspace to answer "how many customers are on each plan". Keep the customers that have no match, and count customers rather than result rows so the answer stays right if an account ever gains a second workspace:

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

Put that in labs/sql/_stack/query.sql and run tse check.

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: a report quietly missing customers

What the evidence proved

Query	What it proved	What it did not prove
SELECT count(*) FROM customers	40 customers exist. The finance team was right	Nothing about the report
The report's own output	It returns 34, and only the smaller plans are short	Where the six went
LEFT JOIN ... WHERE w.id IS NULL	Exactly six customers have no workspace
Their plan values	All six are on smaller plans, which explains the pattern

Establishing the true total first is what made the rest quick. Starting from the report invites you to explain why it is correct.

Root cause

The report joins workspaces to reach data it does not use. An inner join keeps only rows with a match on both sides, so the six customers who have not created a workspace yet are discarded before the count runs.

Nothing errored, because nothing is wrong with the SQL. It is valid, it runs, and it answers a subtly different question: not "how many customers are on each plan" but "how many customers who have a workspace are on each plan".

The pattern the finance team noticed is real and was the best clue in the ticket. The six accounts without workspaces are all recent, and recent accounts skew to smaller plans, so the loss concentrated there.

Scoped fix

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

LEFT JOIN keeps customers with no workspace. COUNT(DISTINCT c.id) keeps the answer correct if an account ever gains a second one, which an inner join against a growing table will eventually cause.

Dropping the join entirely also works here and is arguably cleaner. Keeping it is the more defensive choice if the report is expected to grow workspace columns later.

Customer update

You were right, and the pattern you spotted was the key to it. The report only counts accounts that have created a workspace, and six of ours have not yet. Those six are all recent signups, which is why the shortfall showed up in the smaller plans and the enterprise and growth numbers looked fine.

The report now counts every account regardless of workspace, and returns 40. Nothing was wrong with the underlying account data, so no previous month's account list was affected, only this report's view of it.

Worth checking whether any other report joins through workspaces the same way, because it would undercount identically and just as quietly. Send me the list and I will go through them.

Engineering escalation, if you needed one

You would not escalate this. It resolves at first contact and the fix is a query change. If the same join pattern turns out to be copied across a reporting suite, that is worth raising as a review item rather than an incident.

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 3Before reading the report at all, the true customer total was established independently. Why does that ordering matter?

• It is quicker than reading through the report's definition.
• It fixes an independent reference point, so everything after it measures a gap instead of deciding whether one exists.
• It confirms the customer can be trusted.
• It rules out a problem with the database itself.

Question 2 of 3The query is valid, runs cleanly, and returns the wrong answer. What does "valid" establish here?

• That the underlying data is intact.
• That the fault has to lie in the data rather than in the query.
• That the report was changed recently.
• Only that it is well formed. It answers one question precisely, and that question is not the one in the title.

Question 3 of 3The accounts that vanished all sat on the smaller plans. How should that observation be treated?

• As the strongest clue in the ticket, because a loss with a shape has a mechanism behind it and a random loss usually does not.
• As a coincidence, since the plan has no bearing on the query.
• As evidence that the plan field has been corrupted.
• As proof that smaller accounts are being filtered out deliberately somewhere.

3 questions, none answered yet.

Why this one exists

A join is a filter. An inner join silently discards rows that have no match, so a report can be perfectly valid SQL, run without error, and still answer a different question than the one asked.

In an interview

Wrong numbers are harder than errors, because nothing fails. Candidates who reach for the query text first usually miss it. Establishing the true total independently, then finding where rows are lost, is the move that separates a guess from a diagnosis.

Commands introduced

• psql
• LEFT JOIN
• COUNT(DISTINCT ...)

Evidence layers

• row counts against a known total
• join behavior
• the difference between filtering and reading