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.

select
  order_date,
  region,
  amount,
  sum(amount) over (
    partition by region
    order by order_date
    rows between unbounded preceding
             and current row
  ) as running_total
from orders

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;

A common ORDER BY mistake

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 BY is a grand total within each partition — not a running total. Check whether that is the intended calculation.

An interviewer may ask what a window expression without an ORDER BY computes and how to change it.

Checkpoint

Lessons end at a checkpoint — a graded exercise where one exists for the topic. 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.