SQL & Databases
Topic 9 · Querying data · ⏱ about 3 h

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.

Show:
In this topic
Beginner

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:

WITH order_totals AS ( SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id ), big_orders AS ( SELECT * FROM order_totals WHERE total > 500 ) SELECT COUNT(*) AS big_orders, ROUND(AVG(total), 2) AS avg_big_total FROM big_orders;

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.

Beginner

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.
SELECT name, category, price, RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category, ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg FROM products;
Beginner

The window functions you will use most

FunctionWhat 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
Beginner

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:

WITH ranked AS ( SELECT name, category, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products ) SELECT category, name, price FROM ranked WHERE rn = 1;

You cannot write WHERE ROW_NUMBER() … = 1 directly, because window functions are computed after WHERE. That is why the CTE is needed.

Beginner

Running totals and changes over time

WITH monthly AS ( SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders FROM orders GROUP BY month ) SELECT month, orders, SUM(orders) OVER (ORDER BY month) AS running_total, orders - LAG(orders) OVER (ORDER BY month) AS change_from_last_month FROM monthly;

The first month has no previous month, so LAG gives NULL there.

Try it

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.

Beginner

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)

Exercise 1. Average order value with a CTE

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.

Exercise 2. Dearest product per category

Show the most expensive product in each category: category, name, price.

Exercise 3. Running total of orders

Show, for each month in 2025 (month like 2025-01), the number of orders (orders) and a running_total, in month order.

Beginner

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.
Check yourself

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?

Go deeper

📚 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.

Ready