Skip to content
PromptayOpen the app

SQL query prompt template

An AI prompt for writing correct SQL from a plain-language question: give the schema, the dialect and the edge cases, and get a query with an explanation you can check.

You write

SQL query to find customers who ordered last year but not this year

AI models write SQL quickly and confidently, which is exactly the problem. Without your schema they guess table names, without your dialect they mix PostgreSQL and MySQL syntax, and without the edge cases they return a query that runs fine and answers the wrong question. This template gives the model what a colleague would ask for before writing a single line.

The prompt Promptay builds

Prompt
Role: You are a senior data analyst who writes clear, correct PostgreSQL for business reporting.
Task: Write a query that lists customers who placed at least one order in 2025 but no orders so far in 2026.
Context: Tables: customers(id, name, email, created_at) and orders(id, customer_id, status, total, created_at). Cancelled orders have status = 'cancelled' and should not count. Timestamps are stored in UTC. The result feeds a win-back email campaign.
Format: The SQL in one code block, then a short explanation of each step, then the columns the result returns.
Constraints: Use PostgreSQL syntax. Prefer NOT EXISTS over NOT IN so NULLs cannot break the result. Include each customer once. Add a comment on any assumption you had to make.

Why this prompt works

  • The schema is in the prompt. Real table and column names mean the query runs on the first try.
  • The business rule is explicit. Cancelled orders are the edge case that silently skews win-back lists; naming it gets it handled.
  • The dialect is fixed. Date functions differ between databases, so saying PostgreSQL avoids syntax that will not run.
  • Assumptions are surfaced. Asking for comments on assumptions shows you where to double-check before the list goes to marketing.

How to adapt it

Paste the real CREATE TABLE statements or a short list of columns, including the ones that hold status or soft-delete flags. Change the dialect to yours (BigQuery, MySQL, SQL Server). If performance matters, add the table sizes and existing indexes and ask for an index suggestion. To learn rather than copy, ask the model to explain the query line by line, which is what the related skill below does.

Ideas to try in Promptay:

  • "Monthly recurring revenue by plan for the last 12 months"
  • "Explain what this 80-line SQL query does in plain English"
  • "Find duplicate customer records that differ only in email casing"

Related guides and skills