Natural language → SQL with a schema

Translate plain-English questions into SQL against a provided schema. Few-shot.

The prompt

Copy this verbatim. Replace the {{ … }} placeholders with your values.

<instructions>
Convert the question into a single SQL query against the schema in <schema>.
Use PostgreSQL syntax. Return only the SQL inside <sql> tags, no commentary.
If the question can't be answered from the schema, return inside <sql>:
-- cannot answer from schema
</instructions>

<schema>
users(id, email, signup_date, plan)
orders(id, user_id, total_cents, status, created_at)
refunds(id, order_id, amount_cents, created_at)
</schema>

<examples>
  <example>
    <q>How many users signed up in March 2026?</q>
    <sql>SELECT COUNT(*) FROM users WHERE signup_date >= '2026-03-01' AND signup_date < '2026-04-01';</sql>
  </example>
  <example>
    <q>Top 10 users by lifetime revenue, excluding refunds.</q>
    <sql>SELECT u.id, u.email, SUM(o.total_cents) - COALESCE(SUM(r.amount_cents), 0) AS net_cents
FROM users u
JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
LEFT JOIN refunds r ON r.order_id = o.id
GROUP BY u.id, u.email
ORDER BY net_cents DESC
LIMIT 10;</sql>
  </example>
</examples>

<q>{{ user_question }}</q>

Sample input

Average order value by signup-year cohort, last 30 days only.

Expected output

<sql>SELECT EXTRACT(YEAR FROM u.signup_date) AS cohort_year,
       AVG(o.total_cents) / 100.0 AS avg_order_dollars
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
  AND o.status = 'completed'
GROUP BY cohort_year
ORDER BY cohort_year;</sql>

Notes & tuning tips

• The schema block must list every table and column you want the model to use. Anything else → hallucination.
• Two examples covering simple and complex patterns is the sweet spot. More rarely helps; fewer often breaks join handling.
• Always run the SQL in a read-only role with statement_timeout — never trust generated SQL to be safe.

What this example uses

Tags: <instructions> <examples> <example>

Patterns: few shot

More like this