Window functions compute values across a related set of rows while keeping each row visible. They are ideal for rankings, running totals, period comparisons, and percent-of-group analysis.
- PARTITION BY defines the group, ORDER BY defines sequence, and frame clauses define range.
- Use ROW_NUMBER for strict sequencing and RANK when ties should share positions.
- Check frame defaults because they can change running total behavior.
Why windows are different from GROUP BY
GROUP BY returns one row per group, but Northstar analysts often need row detail plus context, like each order and the customer's lifetime order count. Window functions provide that context without losing row-level visibility.
A window clause can partition by customer_id and order by order_date to compute running revenue per customer in one pass.
Choose ranking and frame rules carefully
For product leaderboards, ROW_NUMBER forces unique positions even when revenue ties. RANK preserves ties and skips next positions, which may better match business expectations.
Running metrics should define frames explicitly, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so behavior is clear when duplicate dates exist.
- Use LAG and LEAD for prior and next row comparisons.
- Keep partitions aligned with business entities.
- Inspect a small sample before scaling to full history.
Compute running paid revenue per customer
orders contains customer_id, order_date, status, and total_amount for Northstar web and store channels.
- Filter to paid orders in a subquery or CTE.
- Select each paid order and add SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
- Review one customer timeline to confirm cumulative growth by order sequence.
Common mistakes
- Using ORDER BY outside OVER and expecting window calculation order to change.
- Forgetting a deterministic tie column in window ORDER BY.
- Assuming default frames always mean running total.
- Replacing a simple GROUP BY metric with an unnecessary window query.
Try one
Which function should you use to compare each order amount to the previous order for the same customer?
Use LAG(total_amount) with PARTITION BY customer_id and a deterministic ORDER BY sequence.
Sources
- PostgreSQL 18 Docs: Window FunctionsOfficial definitions of ranking, offset, and aggregate window functions.