Copilot prompt

Write Complex SQL Queries

Generate complex SQL queries from natural language requirements with CTEs, window functions, and optimizations.

Write SQL queries for the following requirements.

## Database: {{database}}
## Schema:
```sql
{{schema}}
```

## Query Requirements:
{{requirements}}

### Query Writing Standards

For each requirement, write the SQL with:

**1. Readable CTEs for complex logic**
```sql
WITH 
  meaningful_name AS (
    -- Clear comment explaining what this CTE does
    SELECT ...
  ),
  another_step AS (
    SELECT ...
    FROM meaningful_name
  )
SELECT * FROM another_step;
```

**2. Proper handling of NULLs**
Use COALESCE and NULLIF explicitly.
Note any NULL-handling assumptions.

**3. Performance considerations**
Comment on which columns should be indexed for this query.
Use EXISTS instead of IN for large subquery sets.
Note any full table scans to address.

**4. Window functions where appropriate**
ROW_NUMBER, RANK, LAG, LEAD, SUM OVER for running totals.
Partition by with clear reasoning.

**5. Query safety**
All inputs should be parameterized (show with $1, $2 or ? placeholders).
Note any potential for N+1 if this is used in a loop.

### For each query output:
- Query name/description
- The SQL
- Expected output shape (column names and types)
- Index recommendations
- Estimated performance note (fast/moderate/expensive — why)

Fill in: {{database}}, {{schema}}, {{requirements}}. In Loopy you fill these in a form and copy the finished prompt in one click.