Window Functions and Running Calculations
A window function computes over a set of neighbouring rows while keeping every row in the output, which grouping cannot do. Ranking, running totals and comparisons with the previous row all come from partitioning and ordering the window.
What a learner can do afterwards
- Compute a running total per customer with a window rather than a self join
- Rank rows within a partition and handle ties deliberately
- Say what a window function keeps that a group by would have collapsed
1 · Read
A window function computes across neighbor rows while keeping every row in the output. Grouping instead collapses each group to one row. When you need each order next to its running total, only a window keeps both.
Per customer running totals read: SUM(amount) OVER (PARTITION BY customer ORDER BY date). Partition restarts the sum per customer, ordering walks the dates, and each order row stays visible beside its total.
Ranking inside a partition needs a tie plan. RANK leaves gaps after ties, DENSE_RANK packs tight with no gaps, and ROW_NUMBER breaks ties by order without sharing a rank. State the tie rule before you rank.
Ask first what must survive: rows or groups. If detail rows must survive with a calculation beside them, reach for a window. If one row per group is the goal, group instead.
Windows calculate beside surviving rows, partitions restart them, and ties need a stated rule.
2 · Watch
Take it off screen
Where it sits
8 questions wait behind this lesson, each with its answer explained. Every answer feeds the sky: stars light as they are learned, and dim when it is time to come back.