Window Functions
Compute values across a set of related rows without collapsing them.
Questions this answers
- →How do I rank rows within a group?
- →How do I calculate a running total?
- →How do I compare a row to the previous or next one?
- →How do I get each user's first or last event?
- →How do I compute a moving average?
How it works
Window functions are one of the most powerful SQL features — and one of the least known. Unlike GROUP BY, they don't collapse rows. They compute a value across a "window" of related rows while keeping every row in the result. Think of them as adding a column that knows about surrounding rows.
ROW_NUMBER — rank rows
SELECT name, genre, monthly_listeners,
ROW_NUMBER() OVER (
PARTITION BY genre
ORDER BY monthly_listeners DESC
) AS rank_in_genre
FROM artists;💡 PARTITION BY defines the group. ORDER BY defines the rank within that group.
SUM — running total
SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders;
LAG — compare to previous row
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM monthly_revenue;
Ready to practise Window Functions?
Solve real exercises with AI feedback — free to start.
Try it free →