Claude 3.5 Sonnet vs GPT-4o for SQL Query Generation: Which Engine Best Handles Complex Joins and Window Functions?

The SQL Generation Problem

Writing basic SQL queries is the classic party trick for modern LLMs. Ask almost any foundational model to pull a user’s email from a single table with a standard WHERE clause, and it will spit out correct syntax in milliseconds. But in real-world engineering, you are rarely querying single tables.

You are writing analytical queries that cross-reference messy schemas, calculate rolling averages, track user retention cohorts, and resolve nested Common Table Expressions (CTEs). This is where the cracks begin to show. A misplaced comma, an unaliased subquery, or a misunderstood window partition will crash your database parser instantly.

In this evaluation, we pit Anthropic's Claude 3.5 Sonnet against OpenAI’s GPT-4o in a direct, multi-round battle. We wanted to see which model actually understands relational algebra and schema topology, and which one is simply guessing syntax patterns based on training data.

We tested both engines on two highly complex database challenges: a multi-table analytics join with structural ambiguity, and a complex window function calculation designed to test cumulative data processing. Here is how they performed.

Test 1: The Multi-Table Analytics Join

For our first test, we provided both models with a simplified database schema for an e-commerce platform. The schema included five tables: users, orders, order_items, products, and refunds.

We asked the models to write a single PostgreSQL query to calculate the "net revenue retention rate" per product category for the third quarter of 2024. This required: 1. Joining all five tables. 2. Deducting refunded amounts from total sales. 3. Handling instances where a product had zero sales or zero refunds without throwing division-by-zero errors (using COALESCE or NULLIF). 4. Filtering strictly by Q3 2024 timestamps.

Claude 3.5 Sonnet's Performance Claude 3.5 Sonnet handled this test with impressive structural precision. Instead of dumping a massive, nested mess of subqueries, it logically structured the query using cleanly named CTEs.

Sonnet correctly identified that aggregating refunds directly in the main join would cause a fan-out issue (where refund amounts are duplicated because of multiple items in a single order). To prevent this, it wrote a separate CTE to aggregate refunds at the item level before performing the main join. It also correctly used COALESCE(sales, 0) - COALESCE(refunds, 0) to prevent null values from ruining the arithmetic.

GPT-4o's Performance GPT-4o took a more direct, brute-force approach. It skipped the modular CTE structure and went straight for a deep, nested subquery.

While the resulting query was syntactically valid on the surface, it suffered from the exact fan-out bug that Sonnet had anticipated. GPT-4o joined the refunds table directly to the order_items table without pre-aggregating, meaning any order with multiple refunded items would report inflated refund totals. It did, however, correctly use NULLIF to avoid division-by-zero errors, showing a good grasp of defensive SQL programming.

Test 2: The Window Function Dilemma

For our second test, we pushed the models into analytical territory. We wanted to calculate a 7-day rolling average of active daily users (ADU) and identify the single highest-value transaction for each user within that 7-day window.

This task is notoriously difficult for LLMs because it requires a combination of window frame specifications (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and ranking functions (DENSE_RANK or ROW_NUMBER) partitioned by user.

`sql -- The target pattern we were looking for: SELECT user_id, login_date, AVG(daily_active_count) OVER ( PARTITION BY user_id ORDER BY login_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as rolling_7_day_avg FROM user_logins; `

GPT-4o's Performance GPT-4o struggled with the window boundaries. It wrote the standard `AVG(...) OVER (...)` syntax but omitted the frame specification entirely, defaulting to an unbounded preceding range. This meant it calculated a running average from the very first record to the current row, rather than a strict 7-day rolling average.

When calculating the highest-value transaction, it used RANK(), which works fine, but it failed to wrap the ranked results in an outer query. In SQL, you cannot reference a window alias in a WHERE clause immediately (e.g., WHERE transaction_rank = 1 inside the same query block). This produced an immediate runtime syntax error. If you are hitting these kinds of execution blocks with OpenAI models, our OpenAI troubleshooting hub covers how to parse and debug API error states.

Claude 3.5 Sonnet's Performance Claude 3.5 Sonnet nailed the window framing on the first try. It correctly included `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` and even added a helpful comment explaining why it chose this specific window boundary over a time-interval frame, noting potential gaps in daily login data.

To find the highest-value transaction, Sonnet structured the query with a clean CTE that computed the ROW_NUMBER(), then selected from that CTE in a secondary query where the row number equalled 1. This is the standard, clean, and error-free way to execute this logic in PostgreSQL.

Under the Hood: What Makes Them Tick?

To understand what makes these models tick, we have to look at how they process logic. Claude 3.5 Sonnet exhibits a highly structured, systemic approach to relational database design. It writes code like an experienced backend developer who has suffered through debugging production deadlocks and faulty aggregates. It builds modularly, using CTEs to isolate logic before assembling the final query.

GPT-4o behaves more like an accelerated copywriter who happens to know SQL syntax. It rushes to the final answer, occasionally missing the logical pitfalls of relational operations like many-to-many joins. However, GPT-4o is incredibly fast, and for simpler, transactional queries, its speed is highly advantageous.

The Verdict

| Feature | Claude 3.5 Sonnet | GPT-4o | | :--- | :--- | :--- | | Complex CTEs | Exceptional (Modular & readable) | Good (Tends to nest queries deeply) | | Window Functions | Flawless syntax and framing | Good syntax, occasionally misses frame logic | | Fan-Out Prevention | High awareness | Low awareness (Prone to duplicate joins) | | Execution Speed | Moderate | Fast |

For complex data analysis, analytics pipelines, and data warehousing tasks, Claude 3.5 Sonnet is the clear winner. Its structural awareness of database relationships prevents costly analytical errors that can easily pass silent checks and ruin your business metrics.

If you are generating simple CRUD operations or working on rapid, low-latency applications where raw speed is your priority, GPT-4o remains a highly capable tool—just make sure you double-check those outer joins.