Claude 3.5 Sonnet vs GPT-4o for Refactoring Legacy SQL: Which LLM Best Untangles Complex Window Functions Without Breaking?

The Legacy SQL Nightmare

It is the stuff of developer nightmares: a 400-line, undocumented SQL query written in 2018 by an engineer who has long since departed for a life of organic farming. The query is a tangled mess of nested Common Table Expressions (CTEs), obscure window functions, implicit joins, and inconsistent column naming conventions. It runs slowly, blocks other transactions, and now you have been tasked with refactoring it to make it readable, maintainable, and optimized for modern cloud data warehouses.

Naturally, your first instinct is to delegate this cognitive suffering to an AI. But SQL refactoring is a notoriously treacherous task for large language models. A single misplaced comma, an incorrect window partition clause, or a misunderstood table alias won't just throw a syntax error—it can silently alter the grain of your data, leading to corrupt dashboard metrics and confused business analysts.

In this comparison, we pit Anthropic's /platforms/claude (using Claude 3.5 Sonnet) against OpenAI's /platforms/openai (using GPT-4o) to see which model can safely refactor, document, and optimise complex legacy SQL queries without breaking your database pipelines.

Parsing the Spaghetti: Logical Query Execution Mapping

Before an LLM can rewrite a query, it has to build an accurate mental map of how the data flows through the database engine. This is where SQL differs from procedural languages like Python. SQL is declarative; the database engine decides the optimal execution path, but the logical flow is strictly dictated by the order of operations—from FROM and JOIN down to SELECT and WINDOW clauses.

When we fed our legacy query to GPT-4o, its initial instinct was to jump straight to cosmetic cleanup. It immediately renamed columns to look prettier and converted implicit joins (like FROM tableA, tableB WHERE tableA.id = tableB.id) to explicit INNER JOIN syntax. While this is great for readability, GPT-4o had a habit of losing track of nested table aliases inside deep CTE loops. It occasionally swapped table prefixes in the outer query, which would lead to immediate "column not found" compilation errors.

Claude 3.5 Sonnet took a fundamentally different approach. Instead of rushing to rewrite the code, it began by spitting out a structured markdown breakdown of the query's logical execution path. It correctly identified the entry points of the primary datasets, mapped out the cascading CTEs, and pointed out redundant joins that weren't contributing to the final output. Before that legacy code becomes a ticking time-bomb in your production database, Claude's structural analysis gives you the confidence that it actually understands the data flow.

The Window Function Test: Partitioning and Framing Under Pressure

Window functions are the ultimate test of SQL proficiency. Anyone can write a basic ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at), but things get incredibly messy when you introduce complex frame specifications like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or cumulative rolling aggregates alongside offset functions like LEAD and LAG.

Our test query included a particularly nasty cumulative sum that calculated rolling 30-day user engagement cohorts with a dynamic window frame.

GPT-4o struggled with the nuances of the frame specification. It attempted to simplify the query by replacing the explicit ROWS frame with a simplified RANGE clause. While this looked cleaner, it changed the logical behaviour of the query on rows with identical timestamps, converting a precise rolling chronological calculation into an unpredictable group aggregate. This is the kind of silent data corruption that can slip through code review unnoticed.

Claude 3.5 Sonnet handled the window functions with surgical precision. It not only preserved the exact logic of the frame specification but also identified a crucial bottleneck: the original query was performing redundant sorting inside multiple independent window clauses. Claude consolidated these by utilizing a named WINDOW clause at the bottom of the query, a feature that vastly improves readability and reduces compilation overhead on engines like PostgreSQL and BigQuery. You can find detailed definitions of these advanced SQL terms in our technical /glossary.

Query Optimisation: Cosmetic Refactoring vs Performance Reality

Refactoring isn't just about making the code look neat; it is about performance. A pretty query that takes 45 minutes to execute on a massive Snowflake cluster is a failure.

GPT-4o tends to focus heavily on cosmetics. It loves modularising queries into many small, neat CTEs. While this makes the code look incredibly organised, over-modularisation can sometimes confuse the query optimisers of older database engines, leading to materialisation overhead and slower runtimes. GPT-4o also missed opportunities to replace costly distinct statements with cleaner GROUP BY logic.

Claude 3.5 Sonnet demonstrated a much stronger grasp of database internals. In its refactored output, it specifically pointed out where the original author had used inefficient correlated subqueries and replaced them with non-correlated joins. It also left detailed inline comments explaining where the addition of a clustering key or a composite index on the source tables would dramatically speed up the execution of the refactored query.

The Debugging Loop: When the Database Spits Back an Error

No matter how good an LLM is, the first run of a refactored query on live, messy data will occasionally throw an edge-case error. How the models handle these errors determines their real-world utility.

When we gave GPT-4o a syntax error returned by a Redshift engine, it became highly apologetic. It rewrote the query from scratch, often undoing the positive formatting changes it had made in the previous step and introducing new syntax errors. If you find yourself stuck in an endless loop of copy-pasting errors to OpenAI's models, you can find troubleshooting assistance at https://openai-support.com.

Claude 3.5 Sonnet handled errors with logical composure. When presented with a syntax error, it isolated the specific CTE that caused the failure, explained why that specific dialect (Redshift) didn't support the proposed syntax, and provided a targeted hotfix without scrambling the rest of the query. If you need help structuring your Claude system prompts for optimal SQL parsing, check out the Anthropic developers center via https://claude-support.com.

The Verdict: Which LLM Saves Your Database?

While GPT-4o is a fast, highly capable assistant for simple SQL cleanups and basic script conversions, it lacks the architectural rigor required for critical database engineering.

• Claude 3.5 Sonnet is the clear winner for complex SQL refactoring. Its ability to accurately map data flow, respect complex window framing logic, and provide performance-minded optimisations makes it the only model we would trust with mission-critical legacy database logic.