Join Debugging and Grain Traps
This lesson focuses on the most frequent SQL interview failure: wrong totals due to mixed grain joins.
Interview expectation: diagnose quickly, fix correctly, and prove with validation SQL.
Debug Prompt #
The starter query below is intentionally wrong. It inflates revenue by mixing order-level totals with item-level joins.
Find the bug, rewrite the query, and prove your fix.
Loading SQL editor...
Proof Query #
sql
SELECT
o.order_id,
ROUND(o.order_total, 2) AS order_total,
ROUND(SUM(oi.line_total), 2) AS item_total
FROM ecom_orders o
JOIN ecom_order_items oi
ON oi.order_id = o.order_id
WHERE o.order_status = 'completed'
GROUP BY o.order_id, o.order_total
HAVING ROUND(o.order_total, 2) <> ROUND(SUM(oi.line_total), 2);Scoring Criteria #
- identifies grain mismatch clearly
- fixes logic without introducing hidden assumptions
- provides validation evidence
- explains why the bug happened
Warning
Never finalize an interview answer without at least one validation query.