Build the mental model
Debug SQL by checking result grain, base rows, each join, filters, and aggregates in stages rather than rewriting everything at once. A small deterministic fixture with written expected rows makes errors easier to isolate.
Connect it to a real scenario
Use five fixture rows to prove a report multiplies enrollment counts by lesson counts, erases LEFT JOIN rows in WHERE, and compares NULL incorrectly. Deliver the corrected query plus a regression fixture.
Try the working example
sql
-- Buggy: enrollments are multiplied by lessons
SELECT t.title, count(e.enrollment_id) AS enrollments
FROM app.tutorials t
LEFT JOIN app.enrollments e USING (tutorial_id)
LEFT JOIN app.lessons l USING (tutorial_id)
WHERE l.duration_minutes > 0
GROUP BY t.title;
-- One possible correction
SELECT t.title, count(e.enrollment_id) AS enrollments
FROM app.tutorials t
LEFT JOIN app.enrollments e USING (tutorial_id)
WHERE EXISTS (SELECT 1 FROM app.lessons l
WHERE l.tutorial_id = t.tutorial_id
AND l.duration_minutes > 0)
GROUP BY t.tutorial_id, t.title;You should see
You produce a bug explanation, corrected query, and reproducible fixture.5-minute try-it
Run the buggy query, inspect its intermediate row set, and identify exactly where multiplication begins.
One important caution
Do not treat adding `DISTINCT` as a root fix; it can hide a grain or relationship error.
PostgreSQL — Queries — PostgreSQL Global Development Group