Text-to-SQL from a plain question — Prompts

What for: get a working SQL query from a plain-language question — you supply the table schema, you get the query with an explanation.

When to use it

You have a database (PostgreSQL/MySQL/ClickHouse) and a question like "how many / who / over what period", but writing the SQL by hand is slow, or you're not a SQL ninja. The AI plays analytics engineer. The result: one ready query in your dialect plus an explanation of what it counts, with no invented tables or columns.

The prompt (copy and paste)

You are a data engineer. Write ONE SQL query for the dialect <FILL IN: PostgreSQL / MySQL / ClickHouse / SQLite>.

SCHEMA (use ONLY these tables and columns, invent nothing):
<PASTE DDL OR A DESCRIPTION: tables, columns, types, keys and relationships>

QUESTION: "<PASTE YOUR QUESTION IN PLAIN LANGUAGE>".

Rules:
1. If a column or table needed for the answer is missing, do NOT invent it: list what's missing and suggest the closest interpretation.
2. Use explicit JOINs on keys, date filters as [start, end) boundaries, and aggregates with readable aliases.
3. Avoid SELECT * ; select only the columns you need. Add ORDER BY and LIMIT where appropriate.
4. Above the query, put 1-2 comment lines: what it counts and what assumptions were made.
5. SELECT only. No UPDATE/DELETE/DROP. If the question requires changing data, refuse and explain why.

Answer format: first the assumptions comment, then a ```sql block with the query, then "Sanity check:" — one sentence on how to quickly confirm it's right.

Why it works: schema grounding — the model sees the real tables and stops hallucinating column names; the write ban protects production.

Filled-in example

Dialect: PostgreSQL. Schema: orders(id, user_id, created_at, total, status), users(id, country). Question: "revenue by country for May 2026, paid orders only".

What the AI should return: the comment "summing total by country, status='paid', May 2026, boundaries [2026-05-01, 2026-06-01)"; then the query SELECT u.country, SUM(o.total) AS revenue FROM orders o JOIN users u ON u.id = o.user_id WHERE o.status = 'paid' AND o.created_at >= '2026-05-01' AND o.created_at < '2026-06-01' GROUP BY u.country ORDER BY revenue DESC;; and "Sanity check: the total across all countries should match overall paid revenue for May."

Variations

Explain someone else's query. "Here's some SQL — explain step by step what it does and point out potentially slow spots."
Optimisation. "This query is slow on 50M rows. Suggest indexes and rewrite it without subqueries in the SELECT."
For BI. "Package it as a view (CREATE VIEW) for a dashboard; pull the date range out as a separate parameter."

Pro tips

The main thing is handing over the REAL schema (DDL, or at least a column list with types). Without it the model guesses names — and the query fails on the first run.
Always run generated SQL against a test copy or with a LIMIT before trusting the numbers: a syntactically valid query can still count the wrong thing (double counting across a JOIN is the classic).
Hard-code "SELECT only": it's both a safety measure (no accidental DELETE) and a signal to the model not to "help" by modifying data.