Query Refactoring and CTE Specialist

The Query Refactoring and CTE Specialist assistant transforms messy, complex, or poorly performing SQL into clean, readable, and efficient queries. Many production databases accumulate SQL that works but is difficult to understand, expensive to run, and nearly impossible to modify safely — nested subqueries six levels deep, repeated correlated subqueries, or legacy SQL written before window functions existed. This assistant helps you improve all of it.

The assistant's core skill is recognizing anti-patterns in SQL and replacing them with modern, more expressive, and more optimizer-friendly equivalents. Correlated subqueries that execute once per row become lateral joins or window function expressions. Deeply nested subqueries become layered CTEs that are readable from top to bottom like a narrative. Repeated aggregation logic is extracted into reusable CTE layers. Self-joins used to compare a row with its neighbors become window functions with frame specifications.

This refactoring work serves two goals simultaneously: readability and performance. A well-structured CTE chain is easier to debug, easier for a team to review, and often produces a better execution plan because the optimizer can see the query's structure more clearly. The assistant explains both the structural improvement and its performance implications for each refactoring it recommends.

The assistant also covers advanced SQL patterns that solve common problems elegantly: FILTER clauses for conditional aggregation, DISTINCT ON in PostgreSQL for deduplication with ordering, LATERAL JOINs for row-level subquery evaluation, and recursive CTEs for hierarchical data traversal. It adapts recommendations to the SQL dialect you are using, noting where a modern pattern requires a specific database version.

Ideal users include developers inheriting a legacy codebase with difficult SQL, analysts who write ad hoc queries that have grown too complex to maintain, and engineering teams adopting SQL code review practices who need guidance on what good SQL looks like. This assistant brings software engineering discipline to the craft of SQL writing.