HomeCasa Prompts

Write and optimize an SQL query

Get a correct SQL query from a plain-language requirement, with recommended indexes and an explanation of the plan. Designed for ChatGPT, Claude and Gemini. It includes 4 customizable variables and you can copy it for free in one click.

Intermediate · Code · Pending editorial review

Designed for:

Prompt

Act as a database administrator who is an expert in {{engine}}.

Schema (relevant tables and columns):
```
{{schema}}
```

I need a query that: {{need}}
Approximate data volume: {{volume}}

Deliver:
1. The SQL query, formatted and commented.
2. A step-by-step explanation of what each part does.
3. Recommended indexes (CREATE INDEX statements) and why.
4. Possible performance issues and how to check them with EXPLAIN.
5. Edge cases (NULL values, duplicates, time zones) and how the query handles them.

Use only syntax compatible with {{engine}}. The query must be read-only (SELECT): do not modify data. The CREATE INDEX statements are recommendations for me to review and run; keep them separate from the query.

How to use it

Paste only the table structure (CREATE TABLE or column list), never real customer data.

Usage example

Need: the 10 customers with the highest spend in the last 90 days and their number of orders.

Sample output

1. Query

-- Top 10 customers by spend in the last 90 days
SELECT
    c.id,
    c.name,
    c.email,
    SUM(o.total) AS spend_90d,
    COUNT(*)     AS order_count
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL 90 DAY   -- sargable range: uses the index
  AND o.total IS NOT NULL
GROUP BY c.id, c.name, c.email
ORDER BY spend_90d DESC
LIMIT 10;

Orders in the period are filtered first, then grouped by customer and sorted by the sum.

CREATE INDEX idx_orders_created_customer_total
    ON orders (created_at, customer_id, total);

It is a covering index: the date filter scans only the needed range, and customer_id and total are read without touching the table.

4. Checking with EXPLAIN

Run EXPLAIN ANALYZE and confirm orders shows type: range and Using index. If you see type: ALL, it is a full table scan.

5. Edge cases

  • Time zone: NOW() uses the server's zone; if created_at is stored in UTC, use UTC_TIMESTAMP().
  • Ties at position 10: add c.id to the ORDER BY for a stable result…

Key facts

Category
Programming & development · SQL
Output type
Code
Level
Intermediate
Designed for
ChatGPT, Claude and Gemini
Verified in
Pending editorial verification
Variables
Database engine, Schema, What you need and Data volume
Language
English
Price
Free, no sign-up
Updated
Nov 7, 2026

Frequently asked questions

Should I create the suggested indexes straight away in production?

No. Test them first on a staging copy and compare the EXPLAIN output, because every index speeds up some reads but slows down writes and uses disk space.

Do I need to paste real data?

No. The table structure and an approximate row count are enough. Never paste personal or customer data into an AI tool.

What is this prompt for?

Get a correct SQL query from a plain-language requirement, with recommended indexes and an explanation of the plan.

Which AI tools does it work with?

It is designed for ChatGPT, Claude and Gemini. We have not verified it in these tools yet, so results may vary between them.

How do I customize the prompt?

Click “Customize” and fill in the variables (Database engine, Schema, What you need and Data volume). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.

Do I need experience to use it?

It is intermediate: it helps to know the topic so you can provide good context and review the result critically.

Is this prompt free?

Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.

Can I trust the AI output?

Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.

Related prompts