SQL query prompt

What most people type

sql query to get the top 10 customers by total spend last year, tables are orders(id, customer_id, total, created_at) and customers(id, name, email)

It works, but the AI has to guess your database/SQL dialect, so the first answer usually needs another round. The version below asks for exactly what a professional would deliver.

Write a SQL query that returns the top 10 customers by total spend last year.

Tables:
- orders(id, customer_id, total, created_at)
- customers(id, name, email)

Requirements:
- Join orders to customers, sum orders.total per customer, and return customer id, name, email and total spend, ordered from highest to lowest spend.
- "Last year" means the previous full calendar year. Use a date range on created_at (>= start of last year and < start of this year) so an index can be used.
- SQL dialect: [database, e.g. PostgreSQL, MySQL, SQL Server]. Write the date logic for that dialect.
- Say how the query handles ties at 10th place and any orders with a NULL total. If you make an assumption, state it.

Return the query in a code block, then 2-3 lines explaining it.

Replace the [brackets] with your details before you send it: database, e.g. PostgreSQL, MySQL, SQL Server.

Why this version works

• Added dialect placeholder. Date functions and LIMIT syntax differ by database.
• Defined 'last year' as a date range. Avoids ambiguity and keeps the query index-friendly.
• Asked about ties and NULL totals. These are common edge cases in top-N queries.
• Stated output columns and format. Gets a usable query with a short explanation.

What a professional sQL query prompt covers

• SQL dialect placeholder
• Last calendar year definition
• Output columns

Best AI model for this prompt

This is a everyday task: simple join and aggregation; small model handles it, though date logic needs care. You don't need the most expensive model. The cheapest model at each company that handles it well:

Pay-per-use list prices for this prompt and its expected answer (about 300 tokens). On a monthly chat plan, the same choice shows up as more messages before you hit your limit. See today's AI prices.