SQL Generation Prompt AI Prompt

Design a prompt that reliably generates correct SQL from natural language questions about a specific database schema.

Database schema: {{schema_definition}}
SQL dialect: {{dialect}} (PostgreSQL / BigQuery / Snowflake / DuckDB)
Target user: {{user_type}} (data analyst / business user / developer)

1. Schema context injection:
   - Include the full DDL for all relevant tables in the prompt
   - Add a brief description above each table: what it represents and its grain
   - Add a brief description of each column that is not self-explanatory
   - Include sample data (3 rows per table) to help the model understand value formats
   - Specify relationships: 'orders.customer_id is a foreign key to customers.id'

2. Dialect-specific instructions:
   - List the dialect-specific functions to use: 'Use DATE_TRUNC for date truncation, not TRUNC'
   - Specify quoting conventions: 'Quote identifiers with double quotes'
   - Specify NULL handling conventions relevant to this dialect

3. SQL style guidelines (for readable, consistent output):
   - SELECT clause: one column per line, aligned
   - Use CTEs (WITH clauses) for multi-step logic, not nested subqueries
   - Always use explicit JOIN syntax, never implicit comma joins
   - Always qualify column names with table aliases when joining multiple tables
   - Add a comment above each CTE explaining what it computes

4. Ambiguity resolution rules:
   - 'When the question is ambiguous about date range, default to the last 30 days'
   - 'When the question asks for top N without specifying N, use 10'
   - 'When a metric could be calculated multiple ways, choose the simplest correct interpretation and add a SQL comment noting the assumption'

5. Error prevention instructions:
   - 'Never use SELECT * in the final output'
   - 'Always add a LIMIT clause when the question does not specify a row count'
   - 'For aggregations, always include GROUP BY for all non-aggregated columns'

6. Output format:
   - Return only the SQL query
   - No explanation unless explicitly asked
   - Add inline SQL comments for any non-obvious logic

Return: the complete SQL generation prompt, 5 test questions ranging from simple to complex, the correct SQL for each, and a rubric for evaluating SQL correctness.