Use when a query result is about to go into a report that someone will act on.
sql-query-logic-review.md
You are reviewing a query before its output reaches a report. You are not optimising it for speed, you are checking whether it answers the question asked.
The question it is meant to answer: {{QUESTION}}
The query: {{QUERY_SQL}}
Table and column meanings, including the grain of each table: {{SCHEMA_NOTES}}
The result it currently returns: {{CURRENT_OUTPUT}}
Output:
1. What {{QUERY_SQL}} actually computes, in two sentences, in business terms rather than SQL terms.
2. The gap between that and {{QUESTION}}, as a short list.
3. A defect table: Clause or line | Problem | Effect on the numbers, as inflates, deflates, or silently drops rows | Fix. Cover at minimum join fan out, inner joins removing rows, date boundaries and time zones, filters applied before rather than after aggregation, null handling, de-duplication, and mixed units or currencies.
4. For each defect, a check I can run: a small count or comparison that would confirm it.
5. Whether {{CURRENT_OUTPUT}} looks consistent with the defects found, and which figure in it you would distrust first.
Rules:
- Judge grain against {{SCHEMA_NOTES}}. Where the grain of a table is not stated there, list it as a question instead of assuming one row per entity.
- Do not rewrite the whole query. Give the changed clause only.
- Rank defects by how far they move the reported number, not by how easy they are to fix.
Replace each placeholder with your own detail. The more specific you are, the less the model invents.
When do I use this rather than running it and sense checking the total?
When the output is about to reach a report someone acts on. A total can look entirely reasonable and still be wrong by a join fan out. This is not a performance review. It checks whether the query answers the question that was asked, which is a different failure from being slow.
What do I need in front of me?
The question in business terms, the query itself, schema notes including the grain of every table, and the result it currently returns. Paste the grain even for tables you think are obvious, because fan out lives in the ones nobody documents. Where the grain is missing it raises a question rather than assuming.
What comes back?
A two sentence restatement of what the query actually computes in business terms, the gap against your question, a defect table ranked by how far each moves the number, a confirming check per defect, and which figure in the current output to distrust first. Keep that restatement as a comment at the top of the query.
What is the mistake that costs me here?
Fixing everything before running the confirming counts. Change several clauses at once and you will never know which defect mattered or whether the numbers in circulation were wrong by much. The defects are ranked by effect on the reported figure rather than by ease of fix, and that is the order to work in.