SQL Query Generator

# Task
Generate a SQL query for the goal described below. Prioritize correctness, then readability, then performance.

# Inputs
- Database type: {database_type}
- Schema: {schema}
- Goal: {goal}
- Constraints: {constraints}

# Pre-flight check
Before writing the query, identify any ambiguities in the schema or goal. If you find ambiguities, list up to 3 clarifying questions AND provide a query based on the most reasonable assumption (state the assumption explicitly).

# Query rules
1. Write the simplest correct query. Do not over-engineer.
2. Prefer CTEs over nested subqueries when the query has 2+ logical steps.
3. Handle NULLs explicitly — never assume a column is NOT NULL unless the schema says so.
4. If the goal involves aggregation, account for potential duplicates.
5. Use database-specific syntax for the stated database type (e.g., PostgreSQL vs MySQL differences).
6. Add inline comments for any non-obvious logic.

# Performance notes
If the query touches large tables (implied by context or schema), note:
- Which indexes would help
- Whether the query would benefit from pagination
- Any full-table-scan risks

# Output
1. **Clarifying questions** (if any, with stated assumptions)
2. **SQL query** (formatted and commented)
3. **Explanation** (step-by-step, 3-5 sentences)
4. **Edge cases** (what could go wrong: NULLs, empty results, duplicates)
5. **Performance notes** (if relevant)

<context>
  <database_type>{database_type}</database_type>
  <schema>{schema}</schema>
  <goal>{goal}</goal>
  <constraints>{constraints}</constraints>
</context>
<task>Generate a SQL query. Prioritize correctness, then readability, then performance.</task>
<instructions>
  Pre-flight: identify ambiguities. If found, list up to 3 clarifying questions AND provide a query with stated assumptions.

  Query rules:
  - Simplest correct query. No over-engineering.
  - Prefer CTEs over nested subqueries for 2+ logical steps.
  - Handle NULLs explicitly.
  - Account for duplicates in aggregations.
  - Use database-specific syntax.
  - Inline comments for non-obvious logic.

  If large tables: note helpful indexes, pagination potential, scan risks.
</instructions>
<output>
  Return: clarifying questions (with assumptions), SQL query (formatted + commented), explanation (3-5 sentences), edge cases (NULLs, empty results, duplicates), performance notes.
</output>

Database type: {database_type}
Schema: {schema}
Goal: {goal}
Constraints: {constraints}

Pre-flight: identify ambiguities. List up to 3 clarifying questions with stated assumptions.

Query rules: simplest correct query, CTEs over subqueries for multi-step, handle NULLs explicitly, account for duplicates, database-specific syntax, inline comments.

If large tables: note indexes, pagination, scan risks.

Based on the entire content above, return:
1. Clarifying questions (with assumptions)
2. SQL query (formatted, commented)
3. Explanation (3-5 sentences)
4. Edge cases (NULLs, empty results, duplicates)
5. Performance notes