SQL Debug & Fix

The reasoning

The join is correct — it is doing what a join means. What is wrong is that the query then sums a column at *order* grain over rows at *order-tag* grain. One order worth 100 that carries two tags contributes 200.

The fastest confirmation is to compare `COUNT(*)` against `COUNT(DISTINCT order_id)`. If they disagree, the result is not at the grain you think it is, and every additive measure in the query is inflated by exactly that ratio.

The fix below pre-aggregates the tags to one row per order, so the join cannot change the row count. The alternative — `SUM(DISTINCT amount)` — happens to give the right answer here and is a trap: two different orders with the same amount would collapse into one.

The reasoning

A LEFT JOIN emits every left row, filling the right-hand columns with NULLs where nothing matched. WHERE then runs over that result. `WHERE o.status = 'paid'` is evaluated against a NULL status for exactly the rows the LEFT JOIN was protecting, and a comparison against NULL is never TRUE.

Moving the predicate into ON makes it part of matching rather than filtering. Unmatched customers survive with NULL order columns, which is what "including customers who have none" means.

The exception worth naming is `IS NULL`. `WHERE o.id IS NULL` in the WHERE clause is deliberate — that is the anti-join idiom, and it works precisely because it keeps the rows the join could not match.

The reasoning

This is fan-out squared. An order with 2 lines and 2 shipments produces 4 rows: each line amount appears twice and each shipping cost appears twice. The two measures are inflated by *different* factors — line totals by the shipment count, shipping by the line count — which is why no single correction factor fixes the report.

The rule is that two facts at different grains must never be joined directly through their shared dimension. Each one is reduced to the target grain first, and only then combined. That is why a warehouse keeps facts in separate tables and joins pre-aggregated results rather than raw rows.

The fix below aggregates both sides to one row per order before joining. Once each input is at the same grain as the output, the join cannot change the row count and the arithmetic is safe.

The reasoning

`x NOT IN (a, b, NULL)` expands to `x <> a AND x <> b AND x <> NULL`. That last comparison is UNKNOWN, and `TRUE AND UNKNOWN` is UNKNOWN, so the predicate can never be TRUE. The result is empty regardless of the data.

What makes it a debugging problem rather than a knowledge problem is that nothing about the query changed. An order row arrived with a NULL product_id — a data-quality event — and a query that had worked for a year returned zero rows with no error.

`NOT EXISTS` is a correlated existence check rather than a chain of comparisons, so a NULL row in the subquery simply fails to match and is ignored. The `LEFT JOIN ... IS NULL` form behaves the same way. Either is a one-line change that makes the query immune to the next NULL.

The reasoning

The order is FROM, WHERE, GROUP BY, HAVING. By the time HAVING is evaluated, the rows have already been collapsed and the SUM computed — so a condition there decides whether to keep the group, not which rows contributed to it.

On this data the customer with only paid orders looks correct, which is what makes it hard to spot: the query is right for the simple case and wrong for exactly the customers who have a mix. That pattern — correct for some rows — is the signature of a filter on the wrong side of a GROUP BY.

The general rule: put a condition in WHERE unless it needs an aggregate. WHERE shrinks the input before grouping, which is both correct here and cheaper. HAVING is for conditions that can only be expressed after aggregation, such as `COUNT(*) > 1`.

The reasoning

An average is a ratio, and ratios are non-additive: you cannot combine them by averaging unless every group has the same denominator. A region with 1 order counts as much as a region with 99 in the broken version, so the answer drifts toward whatever the small regions did.

The fix is to carry the parts rather than the result. Sum the amounts, sum the counts, and divide once at the top. That composes at any level of aggregation, which is why a well-built aggregate table stores numerator and denominator instead of the ratio.

The same reasoning kills summing a distinct count, and a percentage of a percentage. If a measure cannot be added, it cannot be rolled up — and a daily aggregate table simply cannot answer the monthly question at any accuracy.

The reasoning

The LEFT JOIN is doing its job: it guarantees one output row per customer. `COUNT(*)` then counts that row, so every customer scores at least 1 whether or not they have an order.

`COUNT(o.id)` counts non-NULL values of a column that comes from the right side. For the unmatched customer that value is NULL, so the count is 0. The rule to carry away is: after an outer join, never count `*`.

The same trap applies to SUM. `SUM(o.amount)` returns NULL rather than 0 for a customer with no orders — arguably more honest, and it will surprise anything that adds the column up. Coalescing is a decision to make deliberately rather than reflexively.

The reasoning

The CASE yields NULL for every non-matching row, aggregates ignore NULLs, and SUM over nothing is NULL. So a category with no rows in the period produces a NULL cell rather than a zero — which is arguably the more honest answer.

It matters because NULL and 0 mean different things. NULL says "no refunds were recorded"; 0 says "refunds were recorded and totalled nothing". A dashboard that breaks its line on NULL is telling you something real, and coercing it to zero hides it.

The fix below coalesces, because for a revenue pivot a category with no activity genuinely is zero money. Note what you give up: you can no longer distinguish a quiet month from a month where the refunds feed failed to load. If that distinction matters, keep the NULL and handle it in the chart.

The reasoning

BETWEEN is inclusive of both endpoints — of the *values*, and the value `2026-03-31` as a timestamp is midnight at the start of that day. Every order placed during the 31st is outside the range.

The usual patch is to end at `2026-03-31 23:59:59`, which is wrong again the moment the column has sub-second precision: an order at 23:59:59.4 is still excluded. It is also wrong if the column type ever changes.

The half-open form is correct at any precision and composes: consecutive months tile the timeline with no gap and no overlap, so summing the months equals the year. It is the same convention that makes window boundaries and SCD validity intervals work, and it is worth applying as a general rule rather than a date trick.

The reasoning

With no explicit frame, `SUM(x) OVER (ORDER BY d)` means `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. RANGE is value-based: every row tied on the ordering column is in the current row's frame, so both transactions on the shared date receive the total through the end of that day.

The output is still monotonic and still ends at the right grand total, which is why it survives inspection. It only disagrees on the intermediate rows — and only on the days with more than one transaction.

ROWS is position-based and is what almost everyone means. It also needs a deterministic tiebreaker in the ORDER BY, or which of the tied rows comes first is arbitrary and the intermediate values can change between runs.

The reasoning

A month is a property of an instant *in a particular zone*. Truncating a UTC timestamp answers "which UTC month" and the business asked "which local month" — and those differ for every event within the offset of a boundary.

The size of the error is the offset times the event rate at that hour, every month, in the same direction. That is why it reconciles to within a percent and never exactly, and why it is usually written off as a timing difference for years.

The fix converts first and truncates second. The general rule is the same as for partitioning: convert to the zone the question is asked in, then reduce. And say the zone out loud in the column name or the report header, because two dashboards that disagree about the zone will disagree about the numbers forever.

The reasoning

DD/MM/YYYY sorts by day, then month, then year. Every date whose day-of-month is high compares as "late" regardless of its year. The filter is not broken; the ordering it relies on does not exist.

This is the reason ISO 8601 is the only sane wire format for a date in text: `YYYY-MM-DD` is the one layout where lexicographic and chronological order are the same, which is why string comparison on it works and why every other layout betrays you eventually.

The fix below rebuilds an ISO string from the parts so the comparison means what it looks like. The better fix is upstream — parse on ingest and store a date type, so no consumer has to know the format. Doing it in every query is how one of them gets it wrong.

The reasoning

The dimension keeps history — several rows per customer, each with a validity window. Joining on the key alone matches all of them, or in the common case where the query also filters to the latest, matches the wrong one. Either way, a customer upgraded in May changes how their February orders are reported.

The fix adds a temporal predicate: the order date must fall inside the dimension row's window. The interval is closed at the start and open at the end, so exactly one row matches and consecutive versions tile the timeline without overlapping.

Getting the interval convention wrong is the second bug hiding here. Using `<=` at both ends makes an order placed exactly on a boundary match two versions, which doubles the row and therefore the revenue — fan-out arriving through the time dimension rather than the key.

The reasoning

Consecutive dimension versions share an instant when one ends where the next begins. A closed-closed predicate makes that instant belong to both, so any fact landing exactly on it matches twice, and every additive measure for that fact is doubled.

It is hard to catch because it affects only the facts at a boundary — often a handful out of millions, and only for keys that changed. The total is wrong by a small, irregular amount, which reads as a data problem rather than a query problem.

Half-open intervals are the fix and the general rule: closed at the start, open at the end. The same convention makes date ranges, window frames and partition boundaries compose without gaps or overlaps.

The reasoning

The watermark column is not unique. A batch writes dozens of rows with the same `updated_at`, the previous run read some of them and recorded that value, and the rest are now permanently invisible — their timestamp is not greater and never will be.

Switching to `>=` picks them up and re-reads the ones already processed. That is recoverable if the write is idempotent, which is why `>=` plus a keyed merge is a legitimate, simpler design. Carrying the ids seen at exactly the boundary removes the reprocessing too.

The complete checkpoint is therefore the pair — the watermark *and* the ids at it. Either half alone is wrong: one loses rows, the other repeats them.

The reasoning

The two functions differ only on ties: ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3. A `WHERE rn = 1` filter therefore keeps one row under ROW_NUMBER and every tied row under RANK.

The cruelty is that the duplicates RANK fails to remove are precisely the ones you most wanted gone. A partial replay that differs in some column is deduplicated correctly; an exact replay, identical in every field including the timestamp, is not.

ROW_NUMBER breaks the tie arbitrarily, which makes the query non-deterministic unless you add a stable tiebreaker. Adding the primary key to the ORDER BY costs nothing and makes two runs over identical input produce identical output — which anything that diffs runs will need.

The reasoning

The correlated subquery reads like a targeted operation and is not. `UPDATE orders SET region = (SELECT ...)` means "for every row in orders, compute this". Rows with no match get NULL, and NULL is a value like any other as far as an assignment is concerned.

The guard has to be a separate WHERE clause repeating the same correlation — which is exactly why this form is error-prone, and why `UPDATE … FROM` with a join is preferable where the dialect has it: only matching rows are visited, and the condition is written once.

The other hazard, once you switch to `UPDATE … FROM`, is the opposite one: if the source has more than one row per key the engine picks one arbitrarily and reports success. Deduplicate the source, or use MERGE, which errors on a multi-match in several engines.

The reasoning

Identical mechanics to the SELECT case: one NULL in the subquery makes the whole `NOT IN` predicate unsatisfiable, so the DELETE matches no rows and reports success. Nothing errors and nothing changes.

It is worth pausing on how close this came to the opposite disaster. `DELETE FROM lines WHERE order_id IN (SELECT id FROM orders)` with the NULL present still works fine — plain IN is unaffected. It is specifically the negation that collapses, and a cleanup job that reads "delete what is not referenced" is exactly where a negation lives.

NOT EXISTS is the fix and the default. On a delete it matters more than on a select, because the failure is not a wrong report you can re-run — it is a cleanup that silently stopped working, and the table grows until somebody notices.

The reasoning

The engine does not error and does not apply both. The target row is updated once with a value chosen arbitrarily, and which one can change with the plan, the statistics or an engine upgrade. That is why the symptom is a value that flips without any upstream change.

The fix is to make the source unique per key with a stated survivorship rule — latest wins, highest version wins — before it reaches the write. Doing it inside the upsert keeps the decision visible in the same statement that depends on it.

Better still, assert it: a check that the staging table has no duplicate keys, run before the merge, turns a silent arbitrary choice into a loud failure. MERGE on several engines gives you that assertion for free by erroring on a multi-match, which is a good reason to prefer it for unattended jobs.

The reasoning

The test computes `COUNT(customer_id) WHERE customer_id IS NULL`. Every row that satisfies the filter has a NULL in that column, and COUNT of a column ignores NULLs — so the count is zero by construction, on any data.

It is a self-cancelling assertion, and it looks entirely reasonable in review: the column named in the count is the column being tested. That is precisely why it survives.

The general lesson is that a quality check needs a proof that it can fail. Write the assertion, then deliberately break the data and confirm it goes red. A check that has never failed in a year is either protecting something very stable or is not a check.

The reasoning

**Check the arm before the arithmetic.** `SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END)` returns 0 for a month when no row matched the condition. The sum is not wrong; it summed nothing. So the question is why nothing matched.

**Cause one: the comparison value is not the stored value.** Hand-entered status columns carry `Refunded`, `REFUNDED` and `refunded ` with a trailing space. `=` sees three different values. `LOWER(TRIM(status)) = 'refunded'` fixes it, and `SELECT DISTINCT status` would have shown it in ten seconds.

**Cause two: an outer filter removed the rows before the CASE ever saw them.** A `WHERE status = 'paid'` higher up, or an inner join to a table that has no refund rows, means the refunded arm is evaluated against a row set that cannot contain a refund. The CASE is correct and starved.

**Cause three, and the one that reads differently:** a NULL column instead of a zero one. If the arm has no `ELSE`, an unmatched row yields NULL, `SUM` skips NULLs, and a month with no refunds returns NULL rather than 0.00. A blank cell and a zero cell mean different things to a reader, so decide which you want rather than inheriting it.

**How to find it in under a minute:** drop the pivot and run `SELECT LOWER(TRIM(status)), COUNT(*) FROM orders GROUP BY 1`. If `refunded` is missing from that list, it is cause one or two. If it is there with a healthy count, the arm condition itself is wrong.