SQL Query Prompt That Gets Working Queries
A structured prompt that turns a plain-English data question into a correct SQL query — with schema context, dialect, and a real example output.
Enhanced prompt — ready to use
ROLE
You are a senior SQL developer with expertise in database querying and optimization. Your goal is to write an effective and efficient SQL query based on a given task.
TASK
Write a SQL query that fulfills a specific data retrieval or manipulation requirement. Think through this step by step to ensure the query is logically sound, optimized, and handles potential edge cases such as null values or duplicates.
CONTEXT
The user needs a SQL query for a database task. No specific database schema or table details were provided, so assume a common relational database environment with standard SQL syntax. The audience is a developer or analyst who will use the query for data extraction or reporting.
REQUIREMENTS
- Craft a syntactically correct SQL query tailored to the described or implied data operation.
- Consider best practices such as clear aliasing, filtering, and joins if applicable.
- Think step by step and include handling of potential edge cases.
- Before finalizing, verify your answer for correctness and completeness.
- Assumptions: Since no specific schema or task details were provided, assume a typical example scenario with generic table and column names relevant to the task.
OUTPUT FORMAT
Provide the SQL query in a properly formatted code block with comments explaining its purpose and key elements. Include a brief explanation of how it works and any assumptions made.
or write yours rough and click once
That's what PromptAI produced from this vague prompt: write a sql query
Example result
```sql
-- Purpose:
-- Retrieve a list of customers along with their total orders count and total amount spent.
-- Only include customers who have made at least one order.
-- Handle potential NULL values in order amounts by treating them as zero.
-- Exclude duplicate orders by considering distinct order IDs per customer.
-- Assumptions:
-- - There are two tables: Customers and Orders.
-- - Customers table has: CustomerID (PK), CustomerName, Email.
-- - Orders table has: OrderID (PK), CustomerID (FK), OrderAmount, OrderDate.
-- - OrderAmount can be NULL, indicating no charge or pending order (treated as 0).
-- - A customer may have zero or multiple orders.
-- - Standard ANSI SQL syntax is used.
SELECT
c.CustomerID,
c.CustomerName,
c.Email,
COUNT(DISTINCT o.OrderID) AS TotalOrders,
COALESCE(SUM(o.OrderAmount), 0) AS TotalSpent
FROM
Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY
c.CustomerID,
c.CustomerName,
c.Email
HAVING
COUNT(DISTINCT o.OrderID) > 0
ORDER BY
TotalSpent DESC,
c.CustomerName ASC;
```
---
### Explanation
1. **Tables and Join**:
- We use a LEFT JOIN here to connect customers to their orders. This allows inclusion of customers even if they have no orders—but the `HAVING` clause later filters out those without any orders.
2. **Handling NULLs**:
- `COALESCE(SUM(o.OrderAmount), 0)` ensures that if all order amounts are NULL or no orders exist, the total spent is shown as zero rather than NULL.
3. **Counting Distinct Orders**:
- `COUNT(DISTINCT o.OrderID)` guards against duplicate order rows in case the join produces duplicates from additional joined tables or data anomalies.
4. **Filtering**:
- The `HAVING COUNT(DISTINCT o.OrderID) > 0` clause removes customers with no orders, since the user requested to only include customers with at least one order.
5. **Ordering**:
- Results are ordered by `TotalSpent` descending (highest spender first), then by `CustomerName` ascending for readability.
6. **Assumptions**:
- Common customer and order attributes are used.
- This query can serve as a base for similar reporting needs, adaptable to other schemas by renaming tables and columns.
---
This query is efficient for moderate datasets, uses standard SQL functions, and carefully handles edge cases of null values and duplicates.
How to adapt it
- Give it your real table/column names and the SQL dialect (Postgres, MySQL, etc.).
- Ask for an explanation comment so you can verify the logic before running it.
Have your own rough prompt? Enhance it in one click.
Enhance your own promptOr use the ChatGPT prompt enhancer right inside ChatGPT, the AI prompt enhancer for every other tool, or the prompt enhancer for Claude Code and Cursor in your editor.
More coding prompts
CodingCode Translation Prompt That Preserves BehaviorView promptCodingCoding Interview Prompt for Real PracticeView promptCodingCron Job Prompt That Gets the Schedule RightView promptCodingData Analysis Prompt That Finds the StoryView promptCodingDocker Compose Prompt for Multi-Service StacksView promptCodingExcel Formula Prompt That Just WorksView prompt