Reference

Edge Cases and Debugging Drills

Many candidates can solve the first query but fail on interviewer follow-ups.

This lesson trains follow-up resilience:

handling changing requirements
debugging wrong totals
defending metric logic with validation SQL

Typical Follow-Up Twists #

"Now exclude refunded orders."
"Now include ties in top-N."
"Now return both order-based and revenue-based rates."
"Now this should work for weekly and monthly grain."
"Your totals do not match finance. Find the bug."

Debugging Prompt #

The starter query below is intentionally flawed. It reports net monthly revenue, but order-level totals get duplicated across item rows, and returns are netted at the wrong grain.

Fix it and prove your output with validation SQL. (The category-revenue variant of this trap is covered in Join Debugging and Grain Traps; this one is about netting returns.)

Loading SQL editor...

Proof Query (Required in Interview) #

sql

-- Prove return integrity: every return maps to exactly one order item,
-- and no return amount exceeds its item's line_total (rows = problems)
SELECT
  r.return_id,
  oi.order_id,
  ROUND(r.return_amount, 2) AS return_amount,
  ROUND(oi.line_total, 2) AS line_total
FROM ecom_returns r
LEFT JOIN ecom_order_items oi
  ON oi.order_item_id = r.order_item_id
WHERE oi.order_item_id IS NULL
   OR r.return_amount > oi.line_total;

Answer Framework for Follow-Ups #

When requirements change mid-interview:

restate new requirement in one sentence,
identify which CTE or filter changes,
re-run validation query,
explain impact on metric definitions,
call out any unresolved ambiguity.

Scoring Rubric #

bug diagnosis and grain control (35%)
correctness of SQL fix (30%)
validation evidence quality (20%)
follow-up communication under change (15%)

Warning

Most interview failures happen during follow-up changes, not during the initial query.