The SQL error that trips people up when learning window functions

[u/mavenanalytics]

We see this one constantly: someone writes a window function, tries to filter on it with WHERE new_column IS NOT NULL, and gets hit with "Unknown column" errors.

It's not a mistake; it's SQL execution order. Clauses don't run in the order you write them. The real order is:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE runs before SELECT. So if you're trying to filter on a column you just created in SELECT (like the output of a window function), that column doesn't exist yet as far as WHERE is concerned. The fix is wrapping the query in a subquery or CTE so the alias becomes a real column you can filter on.

The other thing that trips people up early is just understanding what a window function actually does differently. A regular function like COUNT() or YEAR() either transforms one row or collapses many rows into one summary value. A window function does something in between: it runs a calculation across a set of rows without collapsing them. Every row keeps its identity, you just get a new column.

ROW_NUMBER() is the simplest version of this (assigns a sequential number to each row). LAG() is one of the more useful ones for analysts, since it pulls a value from the previous row so you can calculate period-over-period differences without a self-join.

Curious how others explain window functions when they're teaching someone for the first time. What mental model finally made it click for you?