Data Analysis & SQL5.0 · 0 ratings

Debug A SQL Query That Returns Wrong Results

Systematically finds the logic error producing incorrect numbers and delivers a corrected, verified query.

Role-BasedChain-of-ThoughtStep-by-Step

Prompt

ROLE: You are a meticulous SQL debugger who finds silent correctness bugs.

CONTEXT: This query returns results that look wrong. Expected behavior: [EXPECTED_RESULT]. Observed behavior: [OBSERVED_RESULT]. Schema and example rows: [SCHEMA_AND_SAMPLE_DATA].
Query:
```sql
[BUGGY_QUERY]
```

TASK (think step by step):
1. List the most common causes of this symptom (join fan-out, INNER vs LEFT join dropping rows, GROUP BY granularity, NULL-eating predicates, integer division, duplicate keys, timezone/boundary errors, DISTINCT masking a join bug).
2. Trace the query against the sample rows to locate where the actual and expected diverge.
3. Identify the single root cause and explain why it produces the observed output.
4. Provide the corrected query.
5. Give a small verification query or test the user can run to confirm the fix.

OUTPUT FORMAT: Root cause (1 paragraph) -> Corrected ```sql``` -> Verification query -> What to watch for next time.

CONSTRAINTS: Change as little as possible to fix the bug. Do not introduce new assumptions about data not shown. If multiple bugs exist, rank them and fix all. State explicitly if the schema/sample provided is insufficient to be certain.

How to use this prompt

  1. 1

    Copy the prompt above and paste it into ChatGPT, Claude, or Gemini — or open it in the visual Studio to edit each part on a canvas and run it with your own key.

  2. 2

    Replace any bracketed placeholders with your specifics. The more concrete your context and constraints, the sharper the result — see the 5-part prompt structure.

  3. 3

    Run it, then refine. Ask the model to critique and improve its own answer with self-critique prompting.

Techniques in this prompt

Role-Based

Assigns the model an expert persona so it adopts the right vocabulary, depth, and standards for the task.

Learn this technique
Chain-of-Thought

Asks the model to reason step by step before answering — ideal for multi-step, logical, or analytical tasks.

Learn this technique
Step-by-Step

Forces explicit intermediate reasoning instead of jumping to a conclusion, which improves accuracy on hard tasks.

Learn this technique

Recommended models

claudegpt-4ogemini

Build on this prompt

Open it in the visual Studio to wire it into a full workflow with your own API key — or learn the craft behind prompts like this.

Open in Studio How to prompt AI correctly

More in Data Analysis & SQL

Translate Business Questions Into SQL

Turns a plain-English stakeholder question into a correct, well-commented SQL query against a known schema.

Read prompt

Optimize A Slow SQL Query

Diagnoses why a query is slow and rewrites it with targeted, explained optimizations and an index plan.

Read prompt

Explain An Unfamiliar SQL Query In Plain English

Reverse-engineers a complex inherited query into a clear narrative, business meaning, and risk list.

Read prompt

Design A Star Schema For Analytics

Produces a dimensional model with fact and dimension tables, grain, keys, and SCD strategy for a reporting need.

Read prompt