# Write a SQL query from plain English

Turns a business question into SQL, with validation checks and an explanation.

- Topic: Data & Analysis
- Framework: RCTFVV
- Level: Advanced
- Tags: Data & Analytics, Write, Analyze, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/write-a-sql-query-from-plain-english

## Prompt

```text
Role: Act as a data analyst writing and validating SQL for a business question.

Context: The business question: [QUESTION]. Schema (tables, key columns, types, relationships, grain): [SCHEMA]. Metric and dimension definitions that apply: [DEFINITIONS]. Known data quirks (soft deletes, timezones, late-arriving data, duplicates): [QUIRKS]. SQL dialect: [DIALECT]. Row-count or spot-check figures I can compare against: [KNOWN_FIGURES].

Goal: Return a query that answers exactly the question asked, with the interpretation and assumptions explicit and a way to confirm the result is right.

Task: Produce: (1) a restatement of the question in precise terms, listing every interpretation choice (time grain, filters, how the metric is defined, how nulls and duplicates are handled); (2) the SQL, readable, with comments on the non-obvious parts; (3) validation steps - checks to run (row counts, totals, a spot-check against a known figure, an edge-case filter) and what each would reveal if wrong; (4) common ways this query could be subtly wrong and how you guarded against them.

Constraints: Use only tables/columns in the provided schema - do not assume columns exist. Handle the stated quirks explicitly. If the question is ambiguous, pick the most likely interpretation, state it, and note the alternative. Show the assumptions, do not bury them.

Examples: Interpretation note: '"Active users last month" = distinct user_id with >=1 event where event_date in the previous full calendar month, excluding is_internal=true and soft-deleted users (deleted_at is null).'

Format: Sections: Question restated + interpretation choices | SQL (commented) | Validation steps | Subtle-error risks & guards.

Verification: For each interpretation choice, say why it is the most defensible reading; flag any part of the question the schema cannot answer.

Validation: Run the validation steps mentally against the known figures: does the result reconcile? If a check would fail, fix the query and say what was wrong.
```

## What to fill in

- QUESTION
- SCHEMA
- DEFINITIONS
- QUIRKS
- DIALECT
- KNOWN_FIGURES

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