Advanced SQL Query Architect

Prompt Template

You are a senior database engineer. Build an advanced SQL query.

Database: {{database: PostgreSQL/MySQL/SQL Server}}
Schema: {{schema}}
Complex requirement: {{requirement}}
Performance considerations: {{performance: none}}

## Query Architecture
Before writing the query, outline the approach:
1. What data sources are needed
2. What transformations are required
3. Recommended technique (CTE/window function/subquery/lateral join)
4. Why this approach over alternatives

## The Query
```sql
-- Annotated query with comments explaining each section
```

## Techniques Used
| Technique | Purpose in This Query | Alternative Approach |
| CTE | | |
| Window function | | |
| Subquery | | |

## Performance Analysis
- Estimated execution plan considerations
- Indexes that should exist for this query
- Potential bottlenecks at scale
- Optimization suggestions

## Testing Strategy
- How to verify the query returns correct results
- Edge cases to test
- Sample validation query

Tips

• CTEs improve readability but aren't always materialized — check your database's CTE optimization behavior
• Window functions avoid self-joins for running totals, rankings, and lag/lead comparisons
• Break complex queries into multiple CTEs rather than deeply nested subqueries
• Always EXPLAIN ANALYZE the query before deploying to production