A running total, a rank within a group, a comparison to the previous row. These used to mean a self-join or a correlated subquery, both slow and awkward to read. Window functions vs subqueries usually comes down to whether you actually need a window function’s ability to see other rows without collapsing the result.
What a window function actually does
A window function computes a value across a set of related rows, a partition, without collapsing them into a single output row the way GROUP BY does. ROW_NUMBER(), RANK(), LAG(), and running sums with SUM() OVER() are the common ones. Every original row stays in the result, each one annotated with something calculated from its window.
Where subqueries used to be the only option
Before window functions were standard, ranking each row within a group meant a correlated subquery, counting how many rows in the same group had a higher value, run once per row. That works, but it’s slow at scale and genuinely hard to read months later. A single window function replaces that pattern in one clean expression.
A real example: where this applies directly
The Olist e-commerce analytics engineering project models a star schema over 99,441 real orders in dbt. Ranking each customer’s orders by date, or comparing an order’s value to that customer’s previous order, are exactly the kind of row-to-row comparisons a window function like LAG() handles cleanly, without needing a self-join back to the same fact table.
When a subquery is still the right tool
Window functions aren’t a universal replacement. A subquery that needs to filter rows before aggregation, or that genuinely needs a separate, independent result set to join against, isn’t something a window function is built to do. The two solve different problems: window functions annotate rows within a result set, subqueries produce a separate result set to be used elsewhere in the query.
Why this matters for dbt models specifically
The retail analytics warehouse and Olist project both model transformations as SQL in dbt. Readable SQL matters more here than in a one-off query, since these models get reviewed, tested, and maintained over time. A window function that replaces a nested correlated subquery isn’t just faster, it’s also easier for the next person reading the model to actually follow.
A quick checklist
- Do you need to keep every original row while adding a calculated value, or do you need a separate, filtered result set?
- Is the current query using a correlated subquery that runs once per row? That’s often a sign a window function would be cleaner and faster.
- Does the transformation need ranking, running totals, or row-to-row comparison within a group? That’s exactly what window functions are for.
- Would the resulting SQL be easier for someone else to read and maintain with a window function instead of a nested subquery?
FAQ
Are window functions always faster than subqueries?
Usually, especially compared to correlated subqueries that execute once per row, but the actual performance depends on the specific query and the database engine’s optimizer.
Can window functions be used inside a WHERE clause?
Not directly. Window function results have to be filtered in an outer query or a common table expression, since WHERE is evaluated before window functions are computed.
Do all SQL databases support window functions?
Most modern databases and warehouses do, including the ones dbt commonly targets, though syntax details can vary slightly between engines.
