Window functions Updated November 2026

PARTITION BY

PARTITION BY col inside an OVER clause divides the rows into groups with the same value and makes the window function work within each group separately, while every row stays in the output: SUM(amount) OVER (PARTITION BY customer_id) puts each customer's total on each of their orders. It is GROUP BY for windows, without collapsing.

Syntax

function() OVER (PARTITION BY col1 [, col2] [ORDER BY cols])

Examples

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Result: name | dept | salary | rank_in_dept Ana | Sales | 6000 | 1 Ben | Sales | 4000 | 2 Carl | IT | 7000 | 1

SELECT id, customer_id, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders;

Result: Each order as a percentage of that customer's total spend

SELECT id, customer_id, amount,
       COUNT(*) OVER (PARTITION BY customer_id) AS orders_by_customer
FROM orders;

Result: Every order row with the customer's order count beside it

With GROUP BY you would get one row per customer; here every order stays.

Common mistakes

Writing PARTITION BY outside OVER or in a GROUP BY query; it only exists inside the OVER clause.
Confusing PARTITION BY with GROUP BY and expecting one row per group; wrap the query and use DISTINCT or GROUP BY if that is what you want.
Forgetting ORDER BY when the function needs an order (RANK, ROW_NUMBER, LAG, running totals); PARTITION BY alone only defines the group.

Practice PARTITION BY

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.
Share of revenueFor each country, return the country and its share of total paid revenue in percent, rounded to 1 decimal.
Each customer's first orderFor each customer, return the customer_id and the id of their first order (earliest date, then lowest id). Count every order, whatever the status.

Questions

What is the difference between PARTITION BY and GROUP BY?

GROUP BY returns one row per group. PARTITION BY computes per-group values but returns all original rows, each annotated with the group value.

Can I use several columns in PARTITION BY?

Yes: PARTITION BY country, city creates one window per distinct country and city combination.

Window functionsWindow functions compute rankings, running totals, shares and previous-row values without collapsing rows. The OVER clause, PARTITION BY, ORDER BY and frames.
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.
GROUP BYGROUP BY collapses rows that share a value into one row per group so you can count, sum or average them. One or several columns, the must-appear rule, HAVING.