A GROUP BY collapses many rows into one. A window function does the calculation without collapsing anything, so each row keeps its own detail and gains a number that was computed across its neighbours. That single difference is what makes rankings, running totals and period-on-period comparisons straightforward.
The OVER clause is the whole idea
Every window function is an ordinary aggregate followed by OVER (...). What goes inside the brackets defines the window: which rows this row is allowed to see, and in what order.
SELECT region,
month,
revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales;- PARTITION BY restarts the calculation per group, the way GROUP BY splits rows, except nothing is collapsed.
- ORDER BY inside the window decides what "so far" means, which is what turns a SUM into a running total.
Three ranking functions, and which to use
They differ only in how they handle ties, and choosing the wrong one is the most common bug in a leaderboard query.
| Function | Ties become | Use it for |
|---|---|---|
| ROW_NUMBER() | 1, 2, 3 | Picking exactly one row per group, such as the latest order per customer. |
| RANK() | 1, 1, 3 | A leaderboard where two equal scores should share a place. |
| DENSE_RANK() | 1, 1, 2 | Grades or bands, where you do not want a gap after a tie. |
SELECT *
FROM (
SELECT customer_id,
order_id,
ordered_at,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY ordered_at DESC) AS rn
FROM orders
) t
WHERE rn = 1;A window function is calculated after WHERE runs, so WHERE ROW_NUMBER() = 1 is invalid. Wrap the query and filter outside it, as above, or use QUALIFY where your database supports it.
Comparing a row with the one before it
LAG reaches backwards and LEAD reaches forwards. Month-on-month growth, which normally needs a self join on a date offset, becomes one line.
SELECT month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_sales;The first row has nothing behind it, so its change is NULL. That is correct, and worth leaving visible rather than filling with a zero that implies no growth.
When a window is the wrong tool
If the answer genuinely has one row per group and no detail is needed, GROUP BY is simpler and faster. Windows earn their cost when you need the detail rows and the summary together, or when the calculation depends on order.
Key takeaways
- PARTITION BY groups without collapsing; ORDER BY inside the window creates the running behaviour.
- ROW_NUMBER for one row per group, RANK for shared places, DENSE_RANK for bands.
- Filter on a window result in an outer query, never in the same WHERE.
- LAG and LEAD replace a self join for any period-on-period comparison.
Turn this into a career, not a bookmark.
Data Science & Analytics with AI takes you from your first query to a portfolio employers read.
Explore the course