Window functions Updated November 2026

Window functions

A window function computes a value across a set of related rows (the window) while keeping every row in the output, unlike GROUP BY which collapses them. OVER defines the window: SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) is a running total per customer. Common ones: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG.

Syntax

function(args) OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause])

Part	What it is
`PARTITION BY`	Splits rows into groups; the function restarts for each group (optional)
`ORDER BY`	Orders rows inside the window; required for ranking, LAG/LEAD and running totals
`frame clause`	ROWS or RANGE BETWEEN ... limits which rows around the current one are included

Examples

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

Every row keeps its own salary and gets the department average next to it.

SELECT id, amount,
       SUM(amount) OVER (ORDER BY id) AS running_total
FROM orders;

Result: id | amount | running_total 1 | 100 | 100 2 | 50 | 150 3 | 75 | 225

SELECT country, SUM(amount) AS revenue,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY country;

Result: Revenue per country and its share of the total

OVER () with no partition is the whole result; a window can wrap an aggregate.

Common mistakes

Using a window function in WHERE (WHERE ROW_NUMBER() OVER (...) = 1) is an error; wrap the query in a CTE or subquery and filter there.
Omitting ORDER BY in the OVER clause for a running total gives the grand total on every row.
Running totals with ORDER BY on a column that has ties use RANGE by default and show the same cumulative value for tied rows; add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Practice Window functions

These drills check your answer on live data, free, no account needed.

Rank within departmentReturn each employee's name, dept_id and their salary rank inside their own department, where 1 is the highest salary.
Running revenue totalFor every paid order, return the order id and the running total of paid revenue, ordered by order date and then by id.
Share of revenueFor each country, return the country and its share of total paid revenue in percent, rounded to 1 decimal.

Questions

What is the difference between window functions and GROUP BY?

GROUP BY returns one row per group. A window function returns the group-level value on every original row, so you can show each order next to its customer total.

Does SQLite support window functions?

Yes, since version 3.25 (2018). PostgreSQL has supported them since 8.4. MySQL added them in 8.0.

Can I name a window and reuse it?

Yes: SELECT ... OVER w FROM t WINDOW w AS (PARTITION BY dept ORDER BY salary). Both SQLite and PostgreSQL support the WINDOW clause.

ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.
RANK and DENSE_RANKRANK() and DENSE_RANK() rank rows and give ties the same rank; RANK skips numbers after a tie, DENSE_RANK does not. Nth highest salary and top N per group.
LAG and LEADLAG() returns a value from a previous row and LEAD() from a following row, without a self join. Month-over-month change, days since the last order, defaults.
Running totalA running total adds each row's value to the sum of all previous rows. SUM() OVER (ORDER BY), per-group totals with PARTITION BY, ROWS frames, moving average.