Skip to content

Intermediate · SQL in depth · topic 23 of 54 · 20 min

Subqueries, CTEs and window functions

Explanation

A subquery is a query inside another query, for example "products priced above the average price". You can use one in WHERE, with IN or EXISTS, or in FROM as a temporary table. A CTE (WITH name AS ...) gives that temporary table a name so long queries read top to bottom. Window functions like ROW_NUMBER rank rows inside groups without collapsing them.

A query inside a query

To find products priced above average you need two steps: work out the average, then compare each product with it. A subquery does the first step inside brackets. The database runs it, gets one number, and uses that number in the outer query.

sql
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;
The inner query returns one value (676.5); the outer query compares each price with it.
Think of it like this: It is like the rough work column on an exam paper. You first work out a small answer on the side (the average), then use it in the main answer. A CTE is the same rough work, but neatly labelled so the examiner can follow it.

IN, EXISTS and NOT EXISTS

A subquery can also return a list. IN checks whether a value is in that list. EXISTS asks a yes or no question for each outer row: "does at least one matching row exist?". A subquery that uses a column of the outer query (o.customer_id = c.id) is called correlated: it is checked once per outer row.

sql
-- customers who bought something from the kitchen category
SELECT name FROM customers
WHERE id IN (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE p.category = 'kitchen'
);

-- customers with no orders at all
SELECT c.name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
IN compares with a list; NOT EXISTS keeps rows with no match.
Careful: Be careful with NOT IN when the subquery can return NULL: x NOT IN (1, 2, NULL) is never true, so the query silently returns nothing. NOT EXISTS does not have this trap, which is why many teams prefer it.

Subqueries in FROM and CTEs

A subquery in FROM acts like a temporary table that exists only for this query. A CTE (Common Table Expression) does the same job with a name, written first with WITH. You can define several, each using the ones before it. Most people find CTEs much easier to read and debug.

sql
WITH totals AS (
  SELECT s.class, s.name, SUM(m.score) AS total
  FROM students s
  JOIN marks m ON m.student_id = s.id
  GROUP BY s.id, s.class, s.name
)
SELECT class, AVG(total) AS class_average
FROM totals
GROUP BY class;
First build a "totals" table per student, then average it per class.

A first look at window functions

GROUP BY squeezes each group into one row. A window function instead adds a value to every row, worked out over a "window" of related rows. OVER (PARTITION BY class ORDER BY score DESC) means: split rows by class, sort each class by score, then number or rank them. SQLite and PostgreSQL both support this.

sql
SELECT s.class, s.name, m.score,
       ROW_NUMBER() OVER (PARTITION BY s.class ORDER BY m.score DESC) AS position
FROM students s
JOIN marks m ON m.student_id = s.id
WHERE m.subject = 'Maths';
Every student keeps their row, and gets a position within their own class.
scoreROW_NUMBERRANKDENSE_RANK
90111
85222
85322
70443
Tip: Top N per group (top scorer per class, latest order per customer) is a classic interview question. The standard answer: a CTE with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), then WHERE row_number = 1 in the outer query.

Watch the lesson

Subqueries, CTEs and window functions · 1/6

Subqueries, CTEs, window functions

Some questions need two steps. Subqueries let one query use the answer of another.

Prefer a video by a teacher? Find free videos on “sql subquery cte window functions row_number tutorial” on YouTube ↗

In real life: Board result toppers on a school portal

When a school portal publishes results, it shows the topper of each section and each student's rank in class. The backend builds a CTE of total marks per student, then uses RANK() OVER (PARTITION BY class ORDER BY total DESC) so students with equal marks share a rank. The same pattern finds the latest order of each customer for a "Buy again" list, or the most recent KYC document per user.

sql
WITH totals AS (
  SELECT s.id, s.class, s.name, SUM(m.score) AS total
  FROM students s JOIN marks m ON m.student_id = s.id
  GROUP BY s.id, s.class, s.name
)
SELECT class, name, total,
       RANK() OVER (PARTITION BY class ORDER BY total DESC) AS class_rank
FROM totals
ORDER BY class, class_rank;
Class rank for every student.

Practice (0/3 done)

Task 1 · Code · SQL

Table: products(id, name, category, price). List the name and price of every product that costs more than the average price of all products. Highest price first. Use a subquery, do not type the average yourself.

Task 2 · Code · SQL

Tables: customers(id, name, city) and orders(id, customer_id, order_date, amount, status). Using EXISTS, list the names of customers who have at least one order with amount above 1000. Sort by name.

Task 3 · Code · SQL

Tables: students(id, name, class) and marks(student_id, subject, score). Find the student with the highest total score in each class. Use a CTE for totals and ROW_NUMBER() OVER (PARTITION BY class ORDER BY total DESC). Show class, name and total, sorted by class.

Quick quiz

Question 1

1.What does WHERE price > (SELECT AVG(price) FROM products) do?

Question 2

2.Why is NOT EXISTS often safer than NOT IN?

Question 3

3.What is a CTE?

Question 4

4.Scores 95, 90, 90, 80 ranked with RANK(). What rank does 80 get?

Question 5

5.How is a window function different from GROUP BY?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Indexes: making queries fast →

← Previous: GROUP BY and HAVING