Window functions
GROUP BY collapses many rows into one summary. A window function does the opposite: it computes across a set of related rows - the window - while keeping every row and its detail. That makes it the tool for running totals, rankings, percentiles, moving averages, and row-to-row comparisons - calculations that otherwise need awkward self-joins or subqueries.
Every box below is live (Run or Ctrl+Enter). Window functions are standard SQL and work everywhere in 2026 - PostgreSQL, SQL Server, MySQL 8+, and SQLite 3.25+ (this sandbox).
OVER: aggregate without collapsing
Add an OVER (...) clause to an aggregate and it becomes a window function. With ORDER BY inside OVER, the aggregate accumulates - a running total - while each order keeps its own row:
The SUM is computed over "all rows up to this one, ordered by date." Without OVER you would get a single grand total; with it, you keep all three rows and the cumulative figure.
PARTITION BY: a window per group
PARTITION BY restarts the window for each group - like GROUP BY, but without collapsing. Here the running total resets per customer:
Ranking functions
Ranking functions number rows within the (ordered) window. The three you will use most differ only in how they treat ties:
ROW_NUMBER- a unique number per row, no ties (1, 2, 3, ...).RANK- ties share a rank, then it skips (1, 1, 3).DENSE_RANK- ties share a rank, no gap (1, 1, 2).
Ana and Ben tie at 90: RANK puts Cleo at 3 (skipping 2), DENSE_RANK puts her at 2 (no gap), ROW_NUMBER just counts 1-2-3. Related functions divide or position rows: NTILE(n) splits the window into n buckets (quartiles, pages), and PERCENT_RANK / CUME_DIST give relative standing.
Offset functions: look at other rows
Offset functions reach to other rows in the window. LAG and LEAD read the previous and next row - perfect for "how did this change from last time?" FIRST_VALUE and LAST_VALUE read the ends of the window.
The first row's prev_total is NULL - there is no earlier row to look back to.
Window frames: which rows the function sees
A frame is the slice of the window a function actually computes over. When you add ORDER BY to OVER, the default frame is "from the start of the partition through the current row" - that is exactly why SUM became a running total earlier. State a frame explicitly to change that.
LAST_VALUE trap - the #1 window bugThat default frame ends at the current row, which quietly breaks LAST_VALUE. LAST_VALUE(total) OVER (ORDER BY created_at) returns the current row's value, not the partition's last - because "the last row of the frame" is the current row, not the last row overall. FIRST_VALUE looks right only because the frame's start is already the partition start. To get the true final value, widen the frame to the whole partition: LAST_VALUE(total) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Whenever you order a window and reach past the current row, set the frame explicitly.
Frame boundaries combine a start and an end with BETWEEN:
UNBOUNDED PRECEDING- the first row of the partition.n PRECEDING/n FOLLOWING-nunits before / after the current row.CURRENT ROW- the current row.UNBOUNDED FOLLOWING- the last row of the partition.
(The start may not come after the end - CURRENT ROW before UNBOUNDED PRECEDING is illegal.) A ROWS frame over the last two rows gives a moving average:
ROWS, RANGE, or GROUPS
The frame unit decides what n counts:
ROWS- a physical count of rows.1 PRECEDING AND CURRENT ROWis exactly two rows.RANGE- by the value of theORDER BYcolumn.RANGE BETWEEN 20 PRECEDING AND 20 FOLLOWINGincludes every row whose value is within 20 of the current one - so the row count varies. PostgreSQL also allows time ranges (RANGE BETWEEN INTERVAL '1 day' PRECEDING AND ...); SQLite supports numeric ranges only.GROUPS- by peer groups of equalORDER BYvalue - movendistinct-value groups, notnrows.
The same query as RANGE, sized by value instead of row count:
Excluding rows, and NTH_VALUE
EXCLUDE trims rows from the frame: EXCLUDE CURRENT ROW drops the current row, EXCLUDE TIES drops its peers but keeps it, EXCLUDE GROUP drops both, and the default EXCLUDE NO OTHERS keeps everything. NTH_VALUE(col, n) returns the n-th row's value in the frame - a generalisation of FIRST_VALUE. Here, each row sees the total of every other order (itself excluded):
When to reach for them
Use a window function whenever you need a per-row calculation that depends on other rows: running totals and cumulative sums, rankings and top-N-per-group, moving averages and trends, or comparing each row to the previous one to find gaps and peaks. They keep these queries flat and readable - no self-joins, no correlated subqueries.
Exercise
Reading window functions and writing them are different skills. Compose these two yourself against the store's orders.
1. Finish it - running total per customer. Show each order's customer_id, id, and total, plus a running total of total within each customer, ordered by date. Fill the blank, then run - the box checks your result.
2. Write it yourself - rank within each customer. Show each order's id, customer_id, and total, plus its rank within that customer by total, biggest first (rank 1 = the customer's largest order). Order by customer_id, then rank. Run it to check.
Show answers
-- 1. running total per customer
SELECT customer_id, id, total,
SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS customer_running
FROM orders
ORDER BY customer_id, created_at;
-- 2. rank within each customer
SELECT id, customer_id, total,
RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders
ORDER BY customer_id, rnk;
PARTITION BY customer_id restarts the window per customer; the OVER clause keeps every order on its own row.
Quick quiz
Window functions
7 questions1What does the OVER clause define for a window function?
2How do RANK and DENSE_RANK differ on ties?
3Which function returns a value from the first row of the sorted window?
4You want each row to show the cumulative total of a column up to that row. What do you use?
5What is a window frame?
6What does UNBOUNDED PRECEDING mean in a frame?
7How does a ROWS frame differ from a RANGE frame?
Data types and dates - the core types, casting, and the date/time handling that trips everyone up.