Subqueries & CTEs

A subquery is a query inside another. It can return one value (compare each score with the overall average), a list (IN (...)), or test for existence (EXISTS).

Common Table Expressions (WITH name AS (...)) name intermediate results so a complex query reads top-to-bottom like steps of a recipe. You can chain several CTEs.

WITH RECURSIVE walks hierarchies — organisation charts, categories, prerequisite chains — by repeatedly joining a table to the previous step's rows.

ctes.sqlSQL

-- Scalar subquery: term-2 Maths scores above the term-2 Maths average
SELECT s.full_name, r.score
FROM results r
JOIN students s ON s.id = r.student_id
WHERE r.term = 2 AND r.subject_id = 1
  AND r.score > (SELECT avg(score) FROM results WHERE term = 2 AND subject_id = 1)
ORDER BY r.score DESC;

-- EXISTS: students with at least one score below 40
SELECT full_name FROM students s
WHERE EXISTS (SELECT 1 FROM results r WHERE r.student_id = s.id AND r.score < 40);

-- CTEs: step-by-step report of term-2 averages and improvement
WITH term_avg AS (
    SELECT student_id, term, avg(score) AS avg_score
    FROM results
    GROUP BY student_id, term
),
improvement AS (
    SELECT t1.student_id,
           round(t1.avg_score, 1) AS term1,
           round(t2.avg_score, 1) AS term2,
           round(t2.avg_score - t1.avg_score, 1) AS change
    FROM term_avg t1
    JOIN term_avg t2 ON t2.student_id = t1.student_id AND t2.term = 2
    WHERE t1.term = 1
)
SELECT s.full_name, i.term1, i.term2, i.change
FROM improvement i
JOIN students s ON s.id = i.student_id
ORDER BY i.change DESC;

-- Recursive CTE: a staff reporting chain
CREATE TEMP TABLE staff (id int PRIMARY KEY, name text, manager_id int REFERENCES staff (id));
INSERT INTO staff VALUES
    (1, 'Head Teacher', NULL), (2, 'Academic Master', 1),
    (3, 'Head of Science', 2), (4, 'Biology Teacher', 3), (5, 'Bursar', 1);

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS depth, name AS path
    FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT st.id, st.name, st.manager_id, c.depth + 1, c.path || ' > ' || st.name
    FROM staff st
    JOIN chain c ON st.manager_id = c.id
)
SELECT repeat('  ', depth) || name AS org_chart, path
FROM chain
ORDER BY path;

Runs in your browser · PostgreSQL

Key points

Subqueries can return a value, a list, or just test existence.
CTEs make multi-step queries readable — name each step.
WITH RECURSIVE handles trees and hierarchies.

Exercise

Using CTEs, find for each form the student with the highest overall average. Then write a recursive CTE that generates the dates of the next 14 days and LEFT JOIN it to payments to show daily totals (zero for days with none).

Show solution

Try the exercise yourself first — then compare your approach with this one.

The first query ranks students' averages within each form in a CTE and keeps rank 1. The second builds 14 consecutive days with a recursive CTE, then LEFT JOINs payments so days without payments still appear with 0.

ctes-solution.sqlSQL

-- The student with the highest overall average in each form
WITH averages AS (
    SELECT s.form, s.full_name, round(avg(r.score), 1) AS average
    FROM results r
    JOIN students s ON s.id = r.student_id
    GROUP BY s.form, s.full_name
),
ranked AS (
    SELECT a.*, rank() OVER (PARTITION BY form ORDER BY average DESC) AS position
    FROM averages a
)
SELECT form, full_name, average FROM ranked WHERE position = 1 ORDER BY form DESC;

-- Daily payment totals for 14 days, including days with no payments
WITH RECURSIVE days AS (
    SELECT date '2026-01-08' AS day
    UNION ALL
    SELECT day + 1 FROM days WHERE day < date '2026-01-21'
)
SELECT d.day, coalesce(sum(p.amount), 0) AS total
FROM days d
LEFT JOIN payments p ON p.paid_at::date = d.day
GROUP BY d.day
ORDER BY d.day;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding