Analyst Debugging Patterns in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Multi-CTE Query Architecture, Join Fanout and Aggregate Correctness, Reading EXPLAIN Output
How do you debug a SQL query?
Analyst debugging is the practice of systematically finding the layer in a complex query where the result went wrong. Every debugging problem is one of three things: wrong results, inflated aggregates, or unexpected NULLs. Each has a specific diagnostic move.
How do you find which CTE broke a query?
Wrong results: isolate each CTE
The most important property of a multi-CTE query for debugging is that each CTE can be run independently. When the final output is wrong, don't read the entire query — run each CTE in isolation and find the first layer where the output diverges from what it should be.
WITH monthly_totals AS (
SELECT
user_id,
date_trunc('month', order_date)::date AS month,
SUM(revenue) AS monthly_revenue
FROM orders
GROUP BY user_id, date_trunc('month', order_date)
)
-- Run this first: is monthly_totals correct for user_id = 42?
SELECT * FROM monthly_totals WHERE user_id = 42 ORDER BY month;WITH monthly_totals AS (
SELECT
date_trunc('month', ordered_at)::date AS month,
SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY date_trunc('month', ordered_at)
)
SELECT * FROM monthly_totals ORDER BY monthComment out all CTEs after the one you're inspecting and SELECT from that CTE directly. The output either matches expectations or it doesn't. If it doesn't, the bug is in that CTE's logic. If it does, move to the next layer. The bug is at the first layer where the output is wrong — everything above is a consequence.
How do you tell whether a join inflated an aggregate?
Inflated aggregates: count rows at the join stage
If a SUM or AVG is producing numbers that are too large, check for fanout before aggregating:
SELECT COUNT(*) AS joined_rows FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;
-- Compare to: SELECT COUNT(*) FROM orders;If joined_rows exceeds the order count, the join is multiplying rows. Any order-level column summed over that result will be inflated. Fix: pre-aggregate the many-side table in a CTE before joining.
How do you trace an unexpected NULL to its source?
Unexpected NULLs: trace to the entry point
Steps through the CTE chain — check whether the NULL is present at each stage. The first layer where the NULL appears identifies its source:
- NULL from a LEFT JOIN with no match: fix with COALESCE at the join output layer
- NULL from arithmetic on a NULL column: fix with
COALESCE(column, 0)before the expression - NULL from a window function operating on NULL input: fix in the CTE that feeds the window layer
What is the one habit behind every SQL debugging move?
The one thing that trips people up
All three diagnostic moves follow the same principle: limit scope before drawing conclusions. The full query is too complex to debug as a unit. Run one CTE, count one join, read one node in the EXPLAIN plan. The method — narrow first, then inspect — works regardless of query length.
A query structured for readability (one operation per CTE, descriptive names) is also a query structured for debugging. The same architecture that makes it legible makes it tractable when it produces wrong results.
Practice Debugging Queries in SQL
Scenario: Brightlane's finance team suspects an order revenue report is inflated because pairing orders with order_items is multiplying the result size. To verify, the analyst wants the count of pairings alongside the total order count for comparison.
Task: Write a query to return two counts: the total number of (order, line-item) pairings on record (joined_row_count), and the total number of orders (order_count).
Assumptions:
- Every line item corresponds to exactly one parent order; an order may have multiple line items.
- The
joined_row_countis the count of (order, line-item) pairings — each line item paired with its parent order. - The
order_countis the count oforders.
Output:
- One row, holding the two counts.
- Columns in this order:
joined_row_count,order_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Debugging Queries practice problems
Write a query to return two counts: the total number of (order, line-item) pairings on record (joined_row_count), and the total number of orders (order_count).
Write a query to return two counts: the total number of (employee, salary) pairings on record (joined_row_count), and the total number of employees (employee_count).
Write a query to return each order's id and its item_count — the number of order_items matched to that order.
Write a query to return each order's id, the recorded_total stored on the order, the item_count of its line items, and the item_total computed from those line items.
Write a query to return each department's id, name, total employee_count, and avg_salary — the average current salary across employees in that department.
Write a query to return each user's user_id and purchase_count — the count of 'purchase' events recorded for that user.
Write a query to return each customer's id, name, and total_revenue — the combined line-item value across all of their orders, reported as a missing value for customers who have no orders on record.
Write a query to return each customer's id, name, and total_revenue — the combined line-item value across their orders, with each line item contributing its value exactly once to the customer's total, reported as a missing value for customers with no orders on record.
Write a query to return each department's id, name, employee_count, and avg_salary — the average current salary across that department's employees — for every department.
Write a query to return two metrics: avg_items_per_order (the average number of order_items per order) and max_items_per_order (the largest number of order_items attached to any single order).
Start learning to practice all 10 Debugging Queries problems, with instant grading and mastery tracking.
Common questions about Debugging Queries
What is the first thing to check when a number looks wrong?
The row count going into the aggregate. Most wrong totals are not arithmetic mistakes but the arithmetic being applied to more rows than you thought, which a join usually caused and which no amount of re-reading the SELECT list will reveal.
How do you check one step of a long query?
Select from that step directly and read what comes out. A query built as named steps can be stopped at any of them, which turns debugging from rereading the whole thing into checking a short list of outputs against what you expected.
Why does narrowing the scope work better than reading harder?
Because a long query has too many places for a mistake to hide, and reading tests your understanding rather than the query. Running one step, counting one join or reading one plan node each replaces an opinion with an observation.
