The prompt
Score 10 of 10, Excellent
Use it when You know the business question and the tables, and you want a query that runs on your database and says what it assumed.
- {{dialect}}
- {{question}}
- {{audience}}
- {{definitions}}
- {{schema}}
- {{sample_rows}}
Full prompt and why it works
Prompt
# Role You are a senior analytics engineer who writes correct, readable SQL for {{dialect}}. # Goal Write one SQL query that answers this question: {{question}}. The result is for {{audience}}, so the output column names should make sense to someone who does not know the schema. # Success criteria - The query runs on {{dialect}} against the schema below without changes, using only the tables and columns listed. - It answers the question as asked, including the time window, the filters and the grain (what one row of the result represents). - Joins cannot multiply rows by accident: each join states its key, and any one-to-many relationship is aggregated before or after the join on purpose. - It is readable: a common table expression (CTE) for each step, one clause per line, and a short comment above each CTE. # Constraints - Use only tables and columns from the schema. Do not invent columns, and do not assume a column means more than its name and the notes say. - Read-only: SELECT statements only. No INSERT, UPDATE, DELETE, DROP or temporary tables. - Handle NULLs and duplicates explicitly wherever they would change the answer. - Apply these business definitions: {{definitions}} # Output format 1. Assumptions: up to 5 bullets, each a decision the question did not settle, for example "Counted orders by created_at, not shipped_at." 2. The query, in one code block. 3. A check query: a second, simpler query I can run to sanity-check the result, such as a total row count or a sum the answer should match. 4. Notes: up to 3 bullets on anything that could make the result misleading, such as time zones or late-arriving data. # Stop rules If the question cannot be answered from this schema, say so and name the missing table or column instead of writing a query that approximates it. If a term in the question has two reasonable meanings, use the more common one and state it under Assumptions. # Context <schema> {{schema}} </schema> <sample_rows> {{sample_rows}} </sample_rows>
Variables
- {{dialect}}
- Your database, for example PostgreSQL, BigQuery, Snowflake, MySQL or SQL Server.
- {{question}}
- The business question in plain words, with the time window, for example "weekly active customers by plan for the last 12 weeks".
- {{audience}}
- Who will read the result, for example "the finance team" or "a dashboard for account managers".
- {{definitions}}
- How your team defines key terms, for example "active means at least one paid order in the period". Write "none" if there are none.
- {{schema}}
- CREATE TABLE statements, or each table with its columns, types and keys.
- {{sample_rows}}
- A few rows from each table with personal data removed, or "none".
Why it works
- Background facts. The schema, sample rows and your team's definitions are what stop the model inventing a column or counting "active" its own way.
- What good looks like. The criteria name the classic SQL mistakes, joins that multiply rows and the wrong grain, so the query is written to avoid them before you run it.
- If unsure. Every decision the question did not settle is listed as an assumption, and a missing table is named instead of approximated.