Revisiting Why SQL’s Order of Execution Matters

A few days ago I thought that the following SQL query would not work— I expected the window function result would be summed multiple times.

🚨 Turns out, I was wrong.

This was a great reminder of why understanding SQL’s order of execution is crucial!

I expected SUM(SUM(val)) OVER (PARTITION BY id) to accumulate incorrectly, but SQL’s execution order ensures that:

1️⃣ The GROUP BY clause first aggregates SUM(val) at the id grain.

2️⃣ Then, the window function is applied to the grouped result—not the raw data. Since there’s only one row per id, the window function correctly returns the expected value.

SQL doesn’t “re-sum” the window function like I feared. Instead, it partitions over the already-aggregated values—exactly as it should.

🔍 Have you ever misjudged a query’s behavior?

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.