The previous lesson introduced window functions — OVER, PARTITION BY, and the ranking trio. Now we go further: running totals that accumulate row by row, LAG/LEAD to peek at neighboring rows, and explicit frame clauses that control exactly which rows feed the window calculation.
The seed has monthly revenue for three SaaS products over six months — small enough to trace every result by hand.
SELECT * FROM monthly_revenue ORDER BY product, month;
Running totals with ORDER BY in the frame
Add ORDER BY inside a window that uses SUM, and Postgres computes a cumulative sum — each row adds itself to the running total:
SELECT product, month, revenue,
sum(revenue) OVER (PARTITION BY product ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY product, month;
For each product the running total grows month by month. January shows just January's number; February shows January + February; and so on. This works because ORDER BY inside OVER implicitly sets the frame to "all rows from the start of the partition up to (and including) the current row."
LAG and LEAD: peeking at neighbors
LAG(expr, offset) returns the value from a previous row in the same partition; LEAD does the same looking forward. Default offset is 1. This is how you answer "what was last month's revenue?" without a self-join:
SELECT product, month, revenue,
lag(revenue) OVER (PARTITION BY product ORDER BY month) AS prev_month,
lead(revenue) OVER (PARTITION BY product ORDER BY month) AS next_month
FROM monthly_revenue
ORDER BY product, month;
January has no previous row, so LAG returns NULL. June has no next row, so LEAD returns NULL. You can supply a default: returns 0 instead of NULL when there's no prior row.