SQL Debugging

The debugging protocol

When handed a broken query, work through it in this order. Out loud. The interviewer is judging the process as much as the answer.

1. Restate what the query is trying to do in one sentence. Often the bug is "it isn't doing what we think it's doing."
2. Sanity-check the row count. Run the input tables' counts. Run the output count. Does it make sense? Funnel queries should not grow rows; aggregations should not have nulls in non-nullable groups.
3. Walk the joins. For each join, what's the cardinality? If both sides are 1-to-many, you're multiplying rows.
4. Walk the filters. Are any WHERE clauses on a LEFT JOIN that secretly turn it into an inner join? Are NULLs being excluded silently?
5. Walk the aggregates. COUNT(*) vs COUNT(col)? COUNT(DISTINCT) when ties are intentional?
6. Walk the windows. Frame defaults? Partitioning? Order ties?
7. Trace one row through by hand. The bug usually reveals itself at a specific step.

1 · NULL traps

NOT IN with subquery NULLs

-- ✗ Returns zero rows if ANY customer in the inner query has referred_by IS NULL
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT referred_by FROM customers
);

-- ✓ Fix: use NOT EXISTS
SELECT c.* FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM customers c2
  WHERE c2.referred_by = c.customer_id
);

Why it breaks: if the subquery returns any NULL, every NOT IN comparison evaluates to UNKNOWN, which filters all rows. NOT EXISTS handles NULLs correctly.

Inequality filters silently dropping NULL rows

-- ✗ Drops rows where tier IS NULL — possibly your "unknown tier" cohort
SELECT * FROM session_summary WHERE tier != 'h100';

-- ✓ Fix: be explicit
SELECT * FROM session_summary WHERE tier IS DISTINCT FROM 'h100';
-- or
SELECT * FROM session_summary WHERE COALESCE(tier, '') != 'h100';

COUNT(col) vs COUNT(*)

• COUNT(*) counts rows (including NULLs in any column).
• COUNT(col) counts non-NULL values of col.
• COUNT(DISTINCT col) ignores NULLs.

Choosing the wrong one is silent — no error, wrong answer.

2 · The "LEFT JOIN turned INNER" trap

This one comes up almost every interview.

-- Intent: every customer with their latest GPU session, even if they have none
-- ✗ The WHERE clause filters out NULL right-side rows → behaves like INNER JOIN
SELECT c.customer_id, g.session_id, g.tier
FROM customers c
LEFT JOIN gpu_sessions g ON g.customer_id = c.customer_id
WHERE g.tier = 'h100';

-- ✓ Fix: move the predicate into the ON clause
SELECT c.customer_id, g.session_id, g.tier
FROM customers c
LEFT JOIN gpu_sessions g
  ON g.customer_id = c.customer_id
 AND g.tier = 'h100';

Tell: if the LEFT JOIN is supposed to preserve "customers with no sessions," the rule of thumb is — predicates on the right table go in ON, predicates on the left table go in WHERE.

3 · Row duplication from many-to-many joins

-- Intent: total revenue per customer
-- Schema: customers (1) → orders (N), each order can have many discount codes (N)
-- ✗ Joining orders to discounts duplicates rows; SUM(amount) over-counts
SELECT c.customer_id, SUM(o.amount) AS revenue
FROM customers c
LEFT JOIN orders o      ON o.customer_id = c.customer_id
LEFT JOIN discounts d   ON d.order_id    = o.order_id
GROUP BY c.customer_id;

-- ✓ Fix: aggregate discounts separately, then join
WITH order_discounts AS (
  SELECT order_id, COUNT(*) AS n_discounts FROM discounts GROUP BY order_id
)
SELECT c.customer_id, SUM(o.amount) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_discounts od ON od.order_id = o.order_id
GROUP BY c.customer_id;

Diagnostic: compute row count of the joined result. If it exceeds the left table's row count and you didn't expect that, you've joined a many.

4 · Wrong granularity

The query returns the right shape but the values are at the wrong unit. Common: aggregating over the wrong key.

-- Intent: average revenue per customer
-- ✗ Averages session revenue across sessions, not per customer
SELECT AVG(revenue_per_session) FROM gpu_sessions;

-- ✓ Fix: aggregate to customer level first
WITH per_customer AS (
  SELECT customer_id, SUM(revenue_per_session) AS revenue
  FROM gpu_sessions GROUP BY customer_id
)
SELECT AVG(revenue) FROM per_customer;

"Average" is the worst offender here — pay attention to "average per what."

5 · Window frame defaults

-- ✗ Returns the current row's plan, not the customer's last plan
SELECT
  customer_id, changed_at, plan,
  LAST_VALUE(plan) OVER (PARTITION BY customer_id ORDER BY changed_at) AS last_plan
FROM plan_changes;

-- ✓ Fix: explicit frame
SELECT
  customer_id, changed_at, plan,
  LAST_VALUE(plan) OVER (
    PARTITION BY customer_id
    ORDER BY changed_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_plan
FROM plan_changes;

The default frame when ORDER BY is specified is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Aggregate windows over time work fine; LAST_VALUE and unbounded windows do not.

ROWS vs RANGE

Rolling N-day averages over data with gaps: use ROWS BETWEEN N PRECEDING AND CURRENT ROW. RANGE treats logical ranges (multiple rows with the same timestamp grouped), which silently misbehaves on gaps.

6 · Date math & time zones

Off-by-one in date math

-- ✗ Inclusive vs exclusive end? Depends on dialect
SELECT * FROM gpu_sessions
WHERE started_at BETWEEN '2026-03-01' AND '2026-03-31';

-- ✓ Fix: explicit, half-open
SELECT * FROM gpu_sessions
WHERE started_at >= '2026-03-01'
  AND started_at <  '2026-04-01';

BETWEEN is inclusive of both ends. With timestamps, '2026-03-31' means 2026-03-31 00:00:00, which excludes the last day. Always use half-open intervals on timestamps.

Time zone mismatch

If started_at is in UTC but you bucket by DATE_TRUNC('day', started_at), your "Monday" might include a few hours of West Coast Sunday. Convert at the query layer:

DATE_TRUNC('day', started_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')

7 · Slow queries

Senior DAE work includes "this query is too slow." Six things to check, in order:

1. Read the EXPLAIN plan. Look for sequential scans on tables that should be indexed, hash joins with billions of rows, sort spills.
2. Partition pruning. For date-partitioned tables, ensure your date filter is on the partition column directly, not wrapped in a function (WHERE DATE(started_at) = '2026-03-15' typically defeats pruning; WHERE started_at >= '2026-03-15' AND started_at < '2026-03-16' works).
3. Join order & cardinality. If the optimizer is joining big-to-big before filtering, statistics may be stale. Run ANALYZE; consider explicit hints.
4. Skew. One value of the join/group key has 10× the rows of others — that single partition takes forever. Check the histogram, mitigate with salting or pre-aggregation.
5. Function on indexed column. WHERE LOWER(email) = ... can't use a plain index on email. Either index the function or rewrite.
6. Materialize the bottleneck CTE. Most warehouses re-evaluate CTEs at every reference. If a CTE is expensive and reused, materialize to a temp table.

8 · GROUP BY surprises

Missing aggregation

Selecting columns not in GROUP BY and not aggregated. Postgres rejects, MySQL silently picks an arbitrary value, BigQuery errors. Bug, not feature.

NULLs in GROUP BY

NULL is its own group. SELECT tier, COUNT(*) FROM session_summary GROUP BY tier; will include a "NULL" group. Surprised people forget about it.

GROUP BY 1, 2 ordinal references

Convenient, but if you reorder SELECT, the GROUP BY silently changes. Prefer explicit column names in anything that ships.