CTEs & Window Functions
Write complex queries as readable steps with WITH (common table expressions), then use window functions to rank rows, number them, compare each row with the previous one, and compute running totals, without losing any rows.
WITH: name the steps of a query
A common table expression (CTE) gives a subquery a name at the top of the query, so you can read the query top to bottom like a recipe:
Each CTE can use the ones before it. CTEs do the same job as derived tables, but they are much easier to read, test (run one step at a time) and change. Professionals use them all the time.
Window functions: calculations across rows, keeping every row
GROUP BY squeezes each group into one row. A window function computes across a group of rows but keeps every row, adding the result as a new column. The window is described with OVER (…):
PARTITION BY: split into groups (like GROUP BY, but without squeezing).ORDER BY: the order inside each group, for ranks and running totals.
The window functions you will use most
| Function | What it gives each row |
|---|---|
ROW_NUMBER() | 1, 2, 3… within the window (no ties) |
RANK() | rank with gaps after ties: 1, 2, 2, 4 |
DENSE_RANK() | rank without gaps: 1, 2, 2, 3 |
LAG(col), LEAD(col) | the value from the previous / next row |
SUM(col) OVER (ORDER BY …) | a running total |
AVG(col) OVER (PARTITION BY …) | the group's average, next to every row |
NTILE(4) | which quarter (quartile) the row falls in |
Top N per group
A classic interview question: "the most expensive product in each category". Number the rows in each group in a CTE, then keep number 1:
You cannot write WHERE ROW_NUMBER() … = 1 directly, because window functions are computed after WHERE. That is why the CTE is needed.
Running totals and changes over time
The first month has no previous month, so LAG gives NULL there.
Try it: CTEs and windows
Run each preset. In the window examples, notice that every row stays, with the new value added.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Using WITH, first compute each order's total (sum of quantity × unit_price), then return one column avg_order: the average total, rounded to 2 decimals.
Show the most expensive product in each category: category, name, price.
Show, for each month in 2025 (month like 2025-01), the number of orders (orders) and a running_total, in month order.
Summary
Key words
- CTE
- a named subquery written with WITH, at the top of a query
- window function
- a calculation across related rows that keeps every row
- OVER
- describes the window
- PARTITION BY
- the groups of the window
- ROW_NUMBER / RANK / DENSE_RANK
- numbering and ranking
- LAG / LEAD
- the previous / next row's value
- running total
- SUM(…) OVER (ORDER BY …)
Common mistakes
- Filtering on a window function in WHERE: compute it in a CTE, filter outside.
- Forgetting ORDER BY inside OVER for ranks, LAG or running totals.
- Confusing RANK and ROW_NUMBER when there are ties.
- A comma after the last CTE before the main SELECT.
- Using GROUP BY when you wanted to keep every row: that is a window function's job.
Quiz
1. What is the main advantage of a CTE over a subquery in FROM?
2. How does a window function differ from GROUP BY?
3. Scores 90, 80, 80, 70: what does RANK() give them?
4. Which function gives the previous row's value?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- PostgreSQL Tutorial (official documentation) ↗ SQL concepts, joins, aggregates, transactions and window functions
- Kaggle Learn: Intro to SQL and Advanced SQL ↗ free short courses with exercises on real data