Claude

The SQL Query Generator from Plain English

💻 Coding Helpers

Stop struggling with complex joins and syntax errors. This prompt translates your plain English questions into highly optimized SQL queries. It acts as a Senior Database Architect, handling everything from Window Functions to CTEs, while also suggesting indexes for speed.

Prompt

Act as a Senior Data Engineer and Database Administrator. I need you to generate an optimized SQL query based on my natural language request.
Context:
Dialect: [Dialect]
My Request: [Question]
Database Schema:
SQL


[Table Schema]


Your Task:
Please provide the solution in 3 distinct sections.
Section 1: The Optimized Query
Write the SQL query to answer my request.
Use modern SQL best practices (CTEs over subqueries where appropriate).
Use proper indentation and capitalization (e.g., SELECT, WHERE).
Handle potential edge cases (e.g., NULL values or division by zero).
Section 2: The Logic Breakdown
Briefly explain how the query works in plain English.
Explain the logic behind any Joins, Grouping, or Window Functions used.
Section 3: Performance Check
Recommend one specific Index that would make this query run faster, explaining which columns should be indexed and why.
Output everything in clean Markdown.

Example Output

(This is what the user gets after running the prompt)
Section 1: The Optimized Query
SQL


WITH CustomerSpend AS (
    -- Calculate total spend per user for completed orders
    SELECT
        u.user_id,
        u.email,
        SUM(o.total_amount) as total_spend
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE o.status = 'completed'
      AND o.created_at >= '2024-01-01'
    GROUP BY 1, 2
)
SELECT
    user_id,
    email,
    total_spend,
    RANK() OVER (ORDER BY total_spend DESC) as spend_rank
FROM CustomerSpend
ORDER BY total_spend DESC
LIMIT 5;



Section 2: The Logic Breakdown
CTE (CustomerSpend): I used a Common Table Expression to aggregate the sales data first. This makes the main query cleaner and easier to read.
Filtering: We strictly filter for status = 'completed' to avoid counting failed transactions.
Window Function (RANK): I added a rank column so you can see exactly where they stand, handling ties gracefully (unlike ROW_NUMBER).

Section 3: Performance Check
Recommended Index:
CREATE INDEX idx_orders_user_status_date ON orders (user_id, status, created_at);
Why?
This composite index covers the foreign key join (user_id) and the two columns used in the WHERE clause (status, created_at). This allows the database to filter rows without performing a full table scan.