SQL Query Generator
Prompt
Generate a SQL query for: Database type: {db_type}. Tables: {tables}. Request: {request}. Include: the SQL query, line-by-line explanation, performance notes, index recommendations, and an alternative approach if applicable.Variables
db_type
tables
request
About This Prompt
Generate SQL queries from natural language descriptions with optimization tips.
Example Output
**Request:** Show the top 10 customers by total order value this quarter, with their order count and average order value.
**SQL Query:**
```sql
SELECT
c.customer_id,
c.name,
c.email,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.created_at >= DATE_TRUNC('quarter', CURRENT_DATE)
AND o.status = 'completed'
GROUP BY c.customer_id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 10;
```
**Explanation:**
- Lines 1-6: Select customer info with aggregated order metrics
- Line 7-8: Join customers to their orders
- Line 9: Filter to current quarter only
- Line 10: Only count completed orders
**Performance Notes:**
- Ensure index on orders(customer_id, created_at, status)
- For large tables, consider a materialized view for frequent reporting
Usage Tips
- Specify your database type
- List table structures
- Mention expected data volume
- Ask for index recommendations