Window functions, without the mystery.

Why window functions matter for an analytics engineer

The first time an interviewer asks you to compute a running total of revenue by customer, in order of order date, you have two options. You can write a self-join and pray. Or you can use a window function and ship.

Window functions are the SQL feature that separates the analyst who can write a query from the analytics engineer who can build a metric layer. Every dbt project of any size leans on them: cohorts, rolling aggregates, deduplication, slowly-changing dimensions. If you only learn one advanced SQL feature, learn this one.

The anatomy of a window function

Every window function follows the same shape. Two parts.

SUM(amount) OVER ( PARTITION BY customer_id ORDER BY order_date ) AS running_total

On the left, an aggregate (here, SUM) — but it doesn't collapse rows. On the right, a window definition (OVER (...)) — which tells the aggregate how to group and order the rows it sees.

The output: one row per input row, with the aggregate computed over the matching window. Crucially, you keep the original rows. GROUP BY would have collapsed them.

Three patterns you'll use weekly

1. Running total

The textbook case. Cumulative revenue per customer, in order of purchase. Watch closely how PARTITION BY isolates each customer and ORDER BY defines the cumulative direction.

SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders;

2. Ranked within a group

Top 3 products by revenue in each category. Add ROW_NUMBER() or RANK() and a WHERE on the result.

SELECT * FROM ( SELECT category, product_id, revenue, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn FROM product_sales ) WHERErn <= 3;

3. Period-over-period change

LAG() lets you reach back to the previous row in the ordered window. Useful for week-over-week, month-over-month, cohort retention — anywhere you want the prior value next to the current one.

SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change FROM monthly_revenue;

The one mistake everyone makes

Forgetting ORDER BY inside the window.The database isn't obligated to give you rows in any particular order without it, and most window functions depend on order. SUM() OVER (PARTITION BY x) without ORDER BYis a grand total within each partition — not a running total. That's rarely what you wanted.

In the interview, this is the follow-up question. The hiring manager writes a query without an ORDER BY and asks you what it does and what to change. Know this one cold.

Checkpoint

Every lesson ends with a graded exercise. Here's this one:

What you just learned

• Window functions return one row per input row — they don't collapse like GROUP BY.
• Every window has two parts: an aggregate or ranking function, and an OVER (...) clause.
• PARTITION BY isolates groups; ORDER BY defines the row sequence inside each group.
• Three patterns will carry most of your dbt work: running totals, ranked-within-group, and period-over-period change.
• Always include ORDER BY in the window when the order matters. It almost always does.