Partitions in window functions

Partitions in window functions (calculations that run across a set of rows without collapsing them into one result) divide your data into independent groups so the calculation resets for each one.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Resetting Retail Window Calculations with PARTITION BY

SELECT order_id, category, revenue,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
FROM orders

Result:

order_id	category	revenue	revenue_rank
O2001	Electronics	299.99	1
O2002	Electronics	149.5	2
O2004	Clothing	210.75	1
O2003	Clothing	89.0	2
O2005	Home	175.0	1

Step by step

AS gives a column a friendlier name in the result.

SELECT order_id, category, revenue,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank

FROM tells SQL which table to pull the data from.

SELECT order_id, category, revenue,
       RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS revenue_rank
FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that returns every original row plus a column showing the average revenue per order_id (use a window function so the rows are kept, not collapsed).

SELECT *, AVG(revenue) OVER (PARTITION BY order_id) FROM orders

2. Using store_sales, write a query that returns every original row plus a column showing the average revenue per sale_id (use a window function so the rows are kept, not collapsed).

SELECT *, AVG(revenue) OVER (PARTITION BY sale_id) FROM store_sales

3. Using products, write a query that returns every original row plus a column showing the average price per category (use a window function so the rows are kept, not collapsed).

SELECT *, AVG(price) OVER (PARTITION BY category) FROM products

4. Using employees, write a query that returns every original row plus a column showing the average salary per department (use a window function so the rows are kept, not collapsed).

SELECT *, AVG(salary) OVER (PARTITION BY department) FROM employees

5. Using reviews, write a query that returns every original row plus a column showing the average rating per customer_id (use a window function so the rows are kept, not collapsed).

SELECT *, AVG(rating) OVER (PARTITION BY customer_id) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed