# Catch silent logic errors in a SQL query before sharing results

Finds the bugs that don't throw errors: join fan-out, dropped rows, null handling, and date boundary mistakes that quietly skew numbers.

- Topic: Data & Analysis
- Framework: Few-Shot
- Level: Standard
- Tags: Data & Analytics, Code, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/catch-silent-logic-errors-in-a-sql-query-before-sharing-results

## Prompt

```text
Check my SQL query for silent logic errors - the kind that run fine but return wrong numbers.

**SQL dialect:** [e.g. Snowflake, BigQuery, Postgres, SQL Server]
**Query:** [PASTE]
**What it is supposed to return:** [e.g. "monthly revenue per active customer, March 2026"]
**Table grains and keys:** [e.g. "orders: one row per order; order_items: one row per item"]
**Result I got and why I'm unsure:** [optional]

Check specifically for:
- Join fan-out: joining to a one-to-many table before aggregating, inflating sums.
- Dropped rows: inner joins or WHERE filters on a left-joined table that turn it into an inner join.
- Nulls: NULL in comparisons, NOT IN with nulls, COUNT(col) vs. COUNT(*), nulls in averages.
- Dates and time: inclusive vs. exclusive boundaries, timestamps vs. dates, timezone conversion, partial current periods.
- Duplicates: DISTINCT hiding a join problem, multiple records per entity (history, retries).
- Aggregation: averages of averages, ratios computed before vs. after summing, integer division.
- Window functions: wrong PARTITION BY or ORDER BY, ties in ROW_NUMBER.
- Filters: test/internal accounts, deleted or cancelled records, status values.

Return:
1. A list of issues found: Line or clause | Problem | Effect on the result | Fix.
2. The corrected query, with comments on what changed.
3. 3-5 sanity-check queries I can run to prove the result is right (row counts before/after joins, key uniqueness, a known total to tie out to).

If the query looks correct, say so and explain which risks you checked.
```

## What to fill in

- e.g. Snowflake, BigQuery, Postgres, SQL Server
- PASTE
- e.g. "monthly revenue per active customer, March 2026"
- optional

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.