How to Debug a SQL Query That Returns the Wrong Results

There's a specific kind of SQL problem that's worse than a syntax error.

The query runs.

No red error message. No failed job. No obvious warning.

It just returns the wrong answer.

Maybe your revenue suddenly doubled. Maybe a dashboard shows 180,000 active users when the company only has 90,000 customers. Maybe your conversion rate looks suspiciously good. Or maybe someone in a meeting asks, "Why doesn't this number match Finance?" and now you're staring at a perfectly valid SQL query wondering where everything went wrong.

These are some of the hardest SQL problems because the database has technically done exactly what you asked it to do.

The problem is that what you asked it to do wasn't what you actually meant.

The good news: incorrect SQL results are usually traceable to a small set of issues — wrong grain, duplicated rows after joins, incorrect filters, NULL behavior, aggregation mistakes, date boundaries, or assumptions about the underlying data that aren't true.

This is the debugging framework I use when SQL executes successfully but the numbers don't make sense.

Who This Guide Is For

This isn't a beginner guide to fixing missing commas or misspelled column names.

This is for you if:

• Your SQL runs successfully but the result doesn't match what you expect
• You've had two analysts write different queries for the same metric and get different answers
• Your dashboard numbers don't reconcile with another source
• You regularly work with joins, CTEs, aggregations, window functions, and business metrics
• You're preparing for SQL interviews where interviewers expect you to validate your own solution

This is one of the biggest differences between writing SQL exercises and working with SQL professionally.

In a coding challenge, someone tells you whether your answer is correct.

At work, you are often the person responsible for determining whether the answer is correct.

The Honest Diagnosis: Why Correct SQL Can Produce Wrong Results

Most incorrect SQL results fall into a handful of categories.

1. Your join changed the grain. You thought you were counting customers, but after joining to orders you were counting customer-order combinations.

2. You're counting duplicated records. The data may contain legitimate duplicates, data-quality duplicates, or duplicates introduced by your query.

3. Your filter doesn't mean what you think it means. Dates, NULLs, status fields, and boolean logic are common offenders.

4. Your denominator is wrong. This is one of the fastest ways to create an incorrect conversion rate, retention rate, or percentage.

5. You're aggregating at the wrong level. The query calculates correctly — just not at the business grain you actually need.

6. Your assumptions about the source data are wrong. Maybe user_id isn't unique. Maybe customers can have multiple subscriptions. Maybe an order can have multiple payment records.

The biggest debugging mistake is staring at the entire 80-line query and trying to mentally solve everything at once.

Don't.

Break the query down and prove each assumption one step at a time.

Step 1: Start With the Expected Answer

Before touching the SQL, ask:

What result am I actually expecting?

Not necessarily the exact number.

The shape.

Suppose the business question is:

How much revenue did each customer generate last month?

Before writing anything, you should already know the expected grain: one row per customer.

And probably these columns:

• customer_id
• total_revenue

Now imagine your final query returns:

customer_id | order_id | total_revenue

That's a clue.

You're no longer at one row per customer.

This sounds simple, but many SQL bugs happen because the expected grain was never clearly defined before the query was written.

For every analysis, be able to complete this sentence:

One row in my final result represents __________.

Examples:

• one customer
• one customer per month
• one marketing campaign per day
• one product per country
• one subscription event

If you can't answer that clearly, debugging everything else becomes harder.

Step 2: Check the Row Count After Every Join

If your numbers suddenly look too high, I would inspect the joins almost immediately.

Suppose you start with:

SELECT COUNT(*)
FROM customers;

and get:

100,000

Then:

SELECT COUNT(*)
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

returns:

850,000

That isn't automatically wrong.

One customer can have many orders.

The important question is:

Did you expect the grain to change?

Now imagine you add:

JOIN payments p
    ON o.order_id = p.order_id

and your row count becomes:

1,600,000

Why?

Maybe one order can have:

• multiple payment attempts
• refunds
• failed transactions
• partial payments

If you didn't know that, your revenue query could quietly double-count money.

A useful debugging habit is checking counts incrementally:

SELECT COUNT(*)
FROM customers;

Then:

SELECT COUNT(*)
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

Then:

SELECT COUNT(*)
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN payments p
    ON o.order_id = p.order_id;

Don't just ask whether the SQL runs.

Ask what each join did to the dataset.

Step 3: Compare COUNT(*) With COUNT(DISTINCT ...)

This is one of the fastest ways to expose a grain problem.

Suppose your query returns:

SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT customer_id) AS customer_count
FROM customer_orders;

Result:

row_count       = 850,000
customer_count  = 100,000

Now you know the dataset contains multiple rows per customer.

Again, that's not necessarily wrong.

But if your downstream query does this:

SELECT COUNT(customer_id)
FROM customer_orders;

and labels it total_customers, you've created a metric bug.

You counted customer-order rows, not customers.

The correct calculation may be:

SELECT COUNT(DISTINCT customer_id)
FROM customer_orders;

The debugging lesson is broader than COUNT(DISTINCT).

Always ask:

What exactly am I counting?

• Rows?
• Customers?
• Orders?
• Sessions?
• Events?
• Subscriptions?

Those are not interchangeable.

Step 4: Validate Your Join Keys

A query can have perfectly valid join syntax and still use the wrong relationship.

Suppose you have:

orders
------
order_id
customer_id
order_date

and:

order_items
-----------
order_id
product_id
quantity

The correct relationship is likely:

ON orders.order_id = order_items.order_id

But imagine someone writes:

ON orders.customer_id = order_items.product_id

The database may allow it if the data types match.

SQL has no idea that the relationship is nonsensical.

A subtler version happens when the join key isn't unique.

Suppose you're joining a user table:

SELECT *
FROM users u
JOIN accounts a
    ON u.email = a.email;

You assumed one email = one user.

But maybe multiple accounts can share the same email.

Now one row unexpectedly matches several.

Before trusting a join key, check uniqueness:

SELECT
    email,
    COUNT(*) AS records
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

If rows come back, email isn't unique.

That doesn't necessarily mean you can't join on it.

It means you need to understand the relationship you're creating.

Step 5: Debug Aggregations Separately

Aggregations hide detail.

That's useful when you're producing final metrics.

It's terrible when you're debugging them.

Imagine this query says revenue is $4.2 million:

SELECT
    SUM(amount) AS revenue
FROM payments
WHERE payment_date >= '2026-07-01'
  AND payment_date < '2026-08-01';

Finance says revenue should be around $3.6 million.

Don't keep changing the SUM().

Look at the rows underneath it.

Start with:

SELECT
    payment_id,
    order_id,
    payment_status,
    amount,
    payment_date
FROM payments
WHERE payment_date >= '2026-07-01'
  AND payment_date < '2026-08-01'
ORDER BY amount DESC;

Now inspect:

• Are failed payments included?
• Are refunds represented as positive values?
• Are duplicate payments present?
• Are test transactions included?
• Are multiple payment attempts being counted?
• Is the date field the correct business date?

Aggregation can make bad assumptions look legitimate because everything gets collapsed into one number.

When an aggregated result is wrong:

Go back to the underlying rows.

Step 6: Check Your Filters One at a Time

Complex WHERE clauses are another common source of silent bugs.

Consider:

WHERE country = 'US'
  AND status = 'active'
  OR plan = 'enterprise'

What does that mean?

SQL operator precedence matters.

This is interpreted differently from:

WHERE country = 'US'
  AND (
      status = 'active'
      OR plan = 'enterprise'
  )

When logic mixes AND and OR, use parentheses deliberately.

Don't make whoever reads the query — including future you — guess what you intended.

Another debugging strategy is to add filters incrementally.

Start:

SELECT COUNT(*)
FROM users;

Then:

SELECT COUNT(*)
FROM users
WHERE country = 'US';

Then:

SELECT COUNT(*)
FROM users
WHERE country = 'US'
  AND status = 'active';

If the number suddenly changes in a way you didn't expect, you've found where to investigate.

Step 7: Remember That NULL Doesn't Behave Like a Normal Value

NULL causes an enormous number of SQL bugs because people mentally treat it like an empty string or zero.

It isn't.

NULL means the value is unknown or missing.

This won't behave the way beginners often expect:

WHERE cancellation_date = NULL

You need:

WHERE cancellation_date IS NULL

Another subtle example:

WHERE country != 'US'

You might think this returns everyone outside the United States.

But rows where country is NULL won't satisfy that condition either.

If the business requirement is everyone who is not in the US, including users with no country recorded, you may need something like:

WHERE country != 'US'
   OR country IS NULL

The same problem shows up in calculations.

Suppose you write price * quantity and quantity is NULL.

The result is NULL.

Not zero.

If the business rule says missing quantity should be treated as zero, you may need:

price * COALESCE(quantity, 0)

But don't blindly COALESCE everything.

First determine what NULL means in the actual business context.

Step 8: Check Your Date Boundaries

Date logic creates some of the most frustrating SQL discrepancies because the query often looks completely reasonable.

Suppose you're calculating July revenue:

WHERE order_date BETWEEN '2026-07-01' AND '2026-07-31'

If order_date is a timestamp, what happens to 2026-07-31 15:30:00?

Depending on the database and implicit casting behavior, your boundary may not behave the way you intended.

For timestamp ranges, I generally prefer:

WHERE order_date >= '2026-07-01'
  AND order_date < '2026-08-01'

This creates a clean half-open interval.

Dates get even more complicated when you're dealing with:

• UTC vs local time
• fiscal calendars
• week boundaries
• month-end timestamps
• daylight saving time
• signup date vs activation date
• event time vs ingestion time

If two reports disagree by a small percentage, date definitions are one of the first things I'd compare.

Step 9: Watch Out for LEFT JOIN Filters

This is one of my favorite SQL debugging interview questions because the query looks correct at first glance.

Suppose you want every customer, plus their completed orders if they have any.

You write:

SELECT
    c.customer_id,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

You used a LEFT JOIN.

But then the WHERE clause removed rows where o.status is NULL.

Which means customers with no orders disappeared.

Your query now behaves much more like an INNER JOIN.

If you truly want every customer, you might write:

SELECT
    c.customer_id,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
   AND o.status = 'completed';

Now the filter is part of the join condition.

This is exactly the kind of issue where:

Valid SQL syntax does not mean correct business logic.

Step 10: Test One Known Entity Manually

When a query becomes complicated, stop testing the entire dataset.

Pick one customer.

One order.

One subscription.

One campaign.

One product.

Then trace that record manually.

Suppose your query calculates customer lifetime revenue.

Pick customer_id = 12345.

Then run:

SELECT *
FROM orders
WHERE customer_id = 12345;

Then:

SELECT *
FROM payments
WHERE order_id IN (
    SELECT order_id
    FROM orders
    WHERE customer_id = 12345
);

Now manually calculate what you believe the answer should be.

Then compare it with your final query.

This dramatically reduces the debugging surface.

It's easier to understand:

Why is customer 12345 showing $420 instead of $300?

than:

Why is total revenue $4.2M instead of $3.6M?

Once you understand the failure on one known record, you can usually generalize it to the entire dataset.

Step 11: Build the Query Back Up in Layers

CTEs are incredibly useful for debugging because they let you inspect transformations one stage at a time.

Suppose your final query looks like:

WITH orders AS (...),
payments AS (...),
customer_revenue AS (...),
ranked_customers AS (...)
SELECT ...

Instead of debugging the final output only, temporarily run:

SELECT *
FROM orders
LIMIT 100;

Then:

SELECT *
FROM payments
LIMIT 100;

Then:

SELECT *
FROM customer_revenue
LIMIT 100;

For every CTE ask:

• What is the grain?
• How many rows are there?
• Are the keys still unique?
• Did NULLs appear?
• Did the row count unexpectedly explode?
• Does this transformation match the business definition?

You're effectively unit-testing your SQL pipeline.

And that's a much more reliable debugging strategy than staring at everything at once.

A Practical SQL Debugging Framework

When SQL runs successfully but the answer looks wrong, I use this order.

1. Define the expected grain

Complete: One row should represent __________.

2. Write down the expected metric definition

If the metric is "active customers," what exactly counts as active?

If it's revenue:

• gross or net?
• completed payments only?
• refunds included?
• taxes included?
• which currency?
• transaction date or settlement date?

Metric disagreements are often definition disagreements disguised as SQL problems.

3. Check the base table

Validate the raw data before adding joins.

4. Check every join

Measure row counts before and after each relationship.

5. Check uniqueness assumptions

Verify whether the key you think is unique is actually unique.

6. Inspect records before aggregation

Don't debug only the final SUM, COUNT, or average.

7. Validate filters individually

Especially:

• date ranges
• NULLs
• status columns
• AND / OR
• LEFT JOIN conditions

8. Test one known entity

Manually calculate the expected answer for one record.

9. Rebuild the query incrementally

Add one transformation at a time.

10. Reconcile against another trusted source

If possible, compare SQL output vs dashboard vs finance report vs source system.

Differences don't automatically mean your query is wrong.

But they tell you where another definition or assumption may exist.

Before and After: Debugging a Revenue Query

Suppose someone asks:

What was total completed-order revenue by customer last month?

You write:

SELECT
    c.customer_id,
    SUM(o.order_total) AS revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN payments p
    ON o.order_id = p.order_id
WHERE o.order_date >= '2026-07-01'
  AND o.order_date < '2026-08-01'
  AND p.status = 'completed'
GROUP BY c.customer_id;

The result is much higher than expected.

At first glance, the query makes sense.

Then you inspect one order:

SELECT *
FROM payments
WHERE order_id = 98765;

and discover:

payment_id | order_id | status
1          | 98765    | completed
2          | 98765    | completed

Maybe the customer paid in two installments.

Now your join creates two rows for the same order.

And because you're summing o.order_total, the entire order value gets counted twice.

The issue isn't SUM().

The issue is the grain created by the join.

One solution is to identify completed orders first without multiplying the order rows:

SELECT
    c.customer_id,
    SUM(o.order_total) AS revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-07-01'
  AND o.order_date < '2026-08-01'
  AND EXISTS (
      SELECT 1
      FROM payments p
      WHERE p.order_id = o.order_id
        AND p.status = 'completed'
  )
GROUP BY c.customer_id;

Now the existence of a completed payment determines whether the order qualifies without duplicating the order itself.

The important lesson isn't that EXISTS is universally better than a join.

It's:

Understand what each table represents before aggregating across the relationship.

The Top SQL Debugging Mistakes

These show up constantly.

1. Starting with the full query. An 80-line query is much harder to debug than eight 10-line pieces.

2. Assuming the source data is clean. Production data will humble that assumption quickly.

3. Using DISTINCT until the number looks right. That's not debugging. That's hiding evidence.

4. Changing multiple things at once. If the result suddenly becomes correct, you won't know which change fixed it.

5. Not checking row counts. Row-count changes tell you a huge amount about what your transformations are doing.

6. Ignoring NULLs. Missing values can completely change filters, joins, averages, and arithmetic.

7. Trusting a dashboard as absolute truth. The dashboard may use a different definition, date boundary, data source, or refresh schedule.

8. Only validating the happy path. Your query may work perfectly for customers with orders and fail completely for customers with zero orders.

How SQL Debugging Shows Up in Technical Interviews

Interviewers don't only care whether your first query works.

They also want to know whether you can recognize when it doesn't.

You might write a solution and hear:

"How would you validate this?"

A weak answer is:

"I'd run it and see if it works."

A stronger answer is:

"I'd first verify the expected grain and compare the number of rows before and after the joins. I'd test a few known users manually, check edge cases like NULL values and users with no activity, and compare aggregate totals against a trusted baseline if one exists."

Or they might ask:

"What could cause this revenue number to be too high?"

A strong answer might include:

• duplicate transactions
• one-to-many joins multiplying rows
• refunds incorrectly included
• test data
• incorrect date boundaries
• repeated payment attempts
• currency conversion issues

That's what interviewers are trying to evaluate.

Not whether you memorized syntax.

Whether you can reason when the data doesn't behave perfectly.

The Quiet Difference Between Writing SQL and Owning a Metric

Anyone can write:

SELECT
    COUNT(*)
FROM users;

But what does that number mean?

• Registered accounts?
• Unique people?
• Active customers?
• Rows in a table?
• Internal test users included?
• Deleted accounts included?
• Bots included?

This is where analytics stops being a coding exercise.

Strong SQL practitioners don't just produce numbers.

They understand:

• what each row represents
• how the metric is defined
• what assumptions went into it
• what data-quality issues could affect it
• how to prove the result is reasonable

That's why debugging is one of the most valuable SQL skills you can develop.

SQL Debugging Checklist

When a SQL query returns the wrong result, check:

• What should one row in the final output represent?
• What is the exact business definition of the metric?
• Does the base table contain the records you expect?
• Did any join unexpectedly increase the row count?
• Are the join keys actually unique?
• Are you accidentally creating a many-to-many relationship?
• Are you counting rows when you mean to count unique entities?
• Are NULL values affecting filters or calculations?
• Are your date boundaries correct?
• Are time zones involved?
• Is a LEFT JOIN being unintentionally converted by a WHERE filter?
• Are duplicates legitimate or caused by the query?
• Can you reproduce the result manually for one known record?
• Have you inspected every CTE separately?
• Can you reconcile the result with another trusted source?

If you work through those questions systematically, most "mystery" SQL problems stop being mysterious.

What To Practice

Don't only practice writing SQL from a blank screen.

Practice debugging SQL that's already wrong.

Take realistic business questions such as:

• Monthly recurring revenue
• Customer churn
• User retention
• Funnel conversion
• Marketing attribution
• Average order value
• Active users
• Subscription upgrades
• Product adoption

Then intentionally introduce a bug.

Use the wrong join grain.

Include NULLs.

Duplicate an event.

Move a filter from the JOIN condition into the WHERE clause.

Use the wrong denominator.

Then diagnose what happened.

That exercise builds a different skill from solving SQL puzzles.

It teaches you how to reason about data behavior, not just syntax.

And if you're preparing for a technical interview, always ask yourself after solving a problem:

How would I prove this answer is correct?

Because a senior candidate isn't expected to blindly trust their first query.

They're expected to know how to challenge it.

The Mindset Shift

The biggest SQL debugging lesson is this:

A query running successfully doesn't tell you whether it's correct.

It only tells you the database understood your instructions.

Your job is to determine whether those instructions actually represented the business question.

That means constantly asking:

• What does one row represent?
• What changed after this join?
• What exactly am I counting?
• What assumptions am I making about this data?
• How would I prove this number is reasonable?

That's the shift from simply writing SQL to actually trusting the analysis built on top of it.

So when a number looks wrong, don't randomly rewrite the query.

Define the expected grain. Check the joins. Inspect the raw rows. Validate the filters. Test one known case. Build the query back up.

The database isn't trying to trick you.

It's usually doing exactly what you told it to do.

The debugging skill is learning to recognize when that isn't what you meant.