// Lesson 1 of 1 · ~25 min · Intermediate

SQL window functions, by example

After this lesson you can add a per-group average or a ranking to every row of a result, and you will know why you cannot filter on it in WHERE.

You will be able to

Explain how a window function differs from GROUP BY
Write PARTITION BY and ORDER BY inside OVER
Filter on a window result with a subquery

Warm-up · Activity 1 of 7
Warm-up: a table has 10 employees in 3 departments. How many rows does this return?
```
SELECT depname, avg(salary)
FROM empsalary
GROUP BY depname;
```
330110
Predict · Activity 2 of 7
Predict: same table, now with a window function. How many rows?
```
SELECT depname, empno, salary,
       avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
```
10It is an error31
Practice · Activity 3 of 7
Complete the query so each employee is ranked by salary, highest first, within their own department.
```
SELECT depname, empno, salary,
       rank() OVER (____ depname ORDER BY salary DESC)
FROM empsalary;
```
rank() OVER (Your answer depname ORDER BY salary DESC)
Practice · Activity 4 of 7
You need the top earner in each department. Put the pieces of the query in the right order.
1. 1. SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos FROM empsalary
2. 2.SELECT depname, empno, salary
3. 3.WHERE pos = 1;
4. 4.FROM (
5. 5.) AS ranked
Practice · Activity 5 of 7
Spot the bug. Why does this query fail?
```
SELECT depname, empno, salary
FROM empsalary
WHERE rank() OVER (PARTITION BY depname ORDER BY salary DESC) = 1;
```
Nothing, the query is fineWindow functions are not allowed in WHERErank() needs an argumentPARTITION BY must come after ORDER BY
Brain teaser · Activity 6 of 7
Brain teaser: the tie trap. Salaries, in order: 3500, 3900, 4200, 4500, 4800, 4800, 5000. Running total with sum(salary) OVER (ORDER BY salary), using the default frame. What does it show on the FIRST 4800 row?
1610020900257004800
Apply · Activity 7 of 7
Mini-task. Table orders(order_id, customer_id, ordered_at, amount). Write one query that returns every order with: the customer’s running total by date, and the order’s rank by amount within that customer.
Check your work against this list
- Both window functions use PARTITION BY customer_id
- The running total orders by ordered_at with a tie-breaker (for example order_id) and, for strict row-by-row totals, an explicit ROWS frame
- The rank orders by amount DESC
- No window function appears in WHERE

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Key ideas

Rows keep their identity

A window function calculates across a set of rows related to the current row, but unlike an aggregate with GROUP BY, the rows are not collapsed into one. Each output row keeps its own identity, and the calculation appears alongside it.

OVER defines the window

PARTITION BY splits rows into groups that share values. ORDER BY inside OVER sets the order in which the function processes them. With ORDER BY, the default frame runs from the start of the partition to the current row, plus any rows that tie with it (its “peers”), which is where running totals come from.

Only in SELECT and ORDER BY

Window functions are evaluated after WHERE, GROUP BY and HAVING, so they are only allowed in the SELECT list and the query’s ORDER BY. To filter on a window result, compute it in a subquery and filter in the outer query.

Sources

PostgreSQL documentation — Tutorial: Window Functions

Last reviewed September 28, 2026

SQL window functions, by example

Warm-up: a table has 10 employees in 3 departments. How many rows does this return?

Predict: same table, now with a window function. How many rows?

Complete the query so each employee is ranked by salary, highest first, within their own department.

You need the top earner in each department. Put the pieces of the query in the right order.

Spot the bug. Why does this query fail?

Brain teaser: the tie trap. Salaries, in order: 3500, 3900, 4200, 4500, 4800, 4800, 5000. Running total with sum(salary) OVER (ORDER BY salary), using the default frame. What does it show on the FIRST 4800 row?

Mini-task. Table orders(order_id, customer_id, ordered_at, amount). Write one query that returns every order with: the customer’s running total by date, and the order’s rank by amount within that customer.