In what order does a SQL query with window functions run?

If you came here from SQL for Data People, you already know the order a query runs in: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Window functions fit into that order, and knowing where explains almost every surprise in this course.

Window functions are calculated after WHERE, GROUP BY and HAVING, over whatever rows are left. QUALIFY filters right after the windows, and then come ORDER BY and LIMIT.

FROM
the tables
WHERE
removes rows
GROUP BY
gathers rows into groups
HAVING
removes groups
SELECT and windows
windows see only what's left
QUALIFY
filters on window results
ORDER BY
sorts the result
LIMIT
cuts the whole result

The logical order, not the order you write the clauses in.

Three consequences you've already run into:

WHERE can't use a window, because when WHERE runs the window doesn't exist yet (the latest-order page).
QUALIFY can, because it runs afterward.
The ORDER BY inside OVER is part of the window's calculation; the one that sorts what you see is the one at the end.

And a fourth you haven't met yet: whatever WHERE removes, the window never sees.

Try it

The exercise's starter code is supposed to show Centro's second half of the year, with each month's share of the full year. When you run it, July comes out as 15.4% of the year, almost double what it should be. Before you fix it, answer the question below: why? Then fix it without losing the month filter. If you can't see why the percentage changes, ask your Maestro.

Check yourself: The starter code says July is 15.4% of Centro's year, but it's really 8.0%. Why does it come out almost double?

Your turn

Show Centro's months from July through December with mes, ventas and pct_del_año, each month's percentage of Centro's full-year sales, with one decimal. Calculate the percentage before removing months (with WITH or with QUALIFY). Sort by mes. You're done when July shows 8.0 and December 9.8.

Tables: ventas_mensuales (sucursal, mes, ventas)

ventas_mensuales · 35 rows

Column	Type
sucursal Nombre de la sucursal: Centro, Norte o Sur.	VARCHAR
mes Primer día del mes que resume la fila.	DATE
ventas Ventas del mes, en pesos.	BIGINT

First rows

sucursal	mes	ventas
Centro	2025-01-01	165100
Centro	2025-02-01	155700
Centro	2025-03-01	178700

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

WHERE mes >= '2025-07-01' removed the first six months before SUM(ventas) OVER () added anything up. The denominator was the half-year total, not the year's. With WITH, the window runs over all 12 months and the month filter comes afterward. QUALIFY does the same, because QUALIFY runs after the windows. In the sandbox, this version gave July 8.0 and December 9.8, the same as the solution:

SQL · lines 4–5

SELECT mes, ventas,
       ROUND(100.0 * ventas / SUM(ventas) OVER (), 1) AS pct_del_año
FROM ventas_mensuales
WHERE sucursal = 'Centro'           -- the window should see this
QUALIFY mes >= DATE '2025-07-01'    -- only you need to see this
ORDER BY mes;

WHERE decides which rows the window sees; QUALIFY decides which rows you see.

The question worth asking yourself every time: which rows do I want the window to see, and which rows do I want to see? The first is filtered with WHERE; the second, afterward.

Windows on top of a GROUP BY

The diagram has another useful consequence: windows run after GROUP BY, so they can work on groups that are already formed. One query can total by branch and also rank those totals or work out each one's share of the whole. The example below builds it step by step.

Ranking branches by their yearly total

Worked example · 4 steps

We want one row per branch with its 2025 total, its place among the three, and its share of the chain, all in one query.

Work out the next step yourself, then open it to compare.

A LIMIT is not a top-N per group. LIMIT is the last thing to run, and it cuts the whole result. In the sandbox, ranking each branch's months and ending with ORDER BY lugar, sucursal LIMIT 3 returned three rows: December for Centro, December for Norte and December for Sur. If you wanted the top 3 months for each branch, the cut belongs in QUALIFY lugar <= 3, which filters within each partition. That difference is the challenge on the next page.

Interviews ask it directly or in disguise: "in what order does this query execute?", or a query with a WHERE and a window whose result you have to predict before running it. Also "rank the salespeople by total sales," to see whether you reach for a GROUP BY plus a subquery, or put the window right on the aggregate. If you want your Maestro to give you a query to predict, ask.

When a "percent of total" doesn't add up, look for the WHERE. Almost always, the window is seeing fewer rows than you think.

DuckDB: window functions, which run after WHERE and HAVING

DuckDB: the QUALIFY clause

Check yourself: In SELECT sucursal, SUM(ventas) AS total, RANK() OVER (ORDER BY SUM(ventas) DESC) AS lugar FROM ventas_mensuales GROUP BY sucursal, how many rows does the window see?

In what order does a SQL query with window functions run?

Try it

Check yourself: The starter code says July is 15.4% of Centro's year, but it's really 8.0%. Why does it come out almost double?

Windows on top of a GROUP BY

Check yourself: In SELECT sucursal, SUM(ventas) AS total, RANK() OVER (ORDER BY SUM(ventas) DESC) AS lugar FROM ventas_mensuales GROUP BY sucursal, how many rows does the window see?

Check yourself: A query numbers rows with ROW_NUMBER() OVER (ORDER BY ventas DESC) and has no ORDER BY at the end. What order do the rows come out in?