---
name: sql-database-query-architect
description: "Production-ready SQL for a complex data question against your schema: the right joins, CTEs, and window functions for your dialect, with the logic explained and the performance choices justified. Use when a complex data question needs a production-ready SQL query designed against a known schema with CTEs, window functions, and dialect-correct syntax. Do not use for diagnosing why an existing query is slow, or a quick one-table statement. Related topics: coding, sql."
---

# SQL Database Query Architect

Production-ready SQL for a complex data question against your schema: the right joins, CTEs, and window functions for your dialect, with the logic explained and the performance choices justified.

## When to use

Use this skill when a complex data question needs a production-ready SQL query designed against a known schema with CTEs, window functions, and dialect-correct syntax. Do not use it for diagnosing why an existing query is slow, or a quick one-table statement.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Restate the request as the grain of the result set (one row per what) and confirm the schema can produce it.
3. Build the query as named CTEs that each do one thing, joined on the schema's keys, with window functions where rows are compared within a group.
4. Write it in the chosen dialect's syntax for limits, dates, strings, and quoting.
5. Apply each selected performance hint where it changes the plan and note where it would not help.
6. For writes, lead with the WHERE clause and add a preview SELECT; then explain the logic in evaluation order.

## Inputs

- **Database dialect** (choose one: MySQL, PostgreSQL, SQL Server, Oracle, SQLite)
- **Schema** (longer free text)
- **Data request** (longer free text)
- **Operation** (choose one: SELECT, INSERT, UPDATE, DELETE, JOIN, AGGREGATE)
- **Performance hints** (choose any: Use indexes, Partition tables, Apply CTEs, Leverage window functions, Enable query caching)
- **Row limit** (number from 1 to 1000)

## Output

A sql code block with the query, a short explanation in engine evaluation order, and one line per performance hint applied or declined.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### SQL Query Design

Design a SQL query for my data request.

- Database dialect: {{Database dialect}}
- Schema: {{Schema}}
- Data request: {{Data request}}
- Operation: {{Operation}}
- Performance hints: {{Performance hints}}
- Row limit: {{Row limit}}

Restate the request as the exact grain of the result (one row per what) before writing any SQL, because most wrong queries come from a mismatched grain. Build the query in named CTEs that each do one thing, joined on the keys the schema gives, and use window functions where the request compares a row to its group (running totals, rank within a partition, the previous row). Write it in the chosen dialect's syntax: LIMIT versus TOP versus FETCH FIRST, date functions, string concatenation, and quoting. Apply the selected performance hints only where they change the plan, and say why a hint was not used when it would not help. For INSERT, UPDATE, and DELETE, write the WHERE clause first and show a SELECT that previews the affected rows. Keep the query readable: one clause per line, aliases that are words.

Return the SQL in a code block labeled sql, then a short explanation of the query logic in the order the engine evaluates it, then one line per performance choice made or declined.

## Pitfalls

- Joining two one-to-many relations in the same query, which multiplies rows and inflates every aggregate.
- Using LIMIT in SQL Server or Oracle, where the syntax is TOP or FETCH FIRST.
- Writing an UPDATE or DELETE without a WHERE preview, so a typo touches the whole table.
