What you'll practice: breaking a complex query into readable steps with
WITH, chaining multiple CTEs, and writing a recursive CTE for tree-shaped data.
A CTE (a.k.a. "WITH query") is a named sub-query you can reference one or more times in the main query. For most people, CTEs are the single biggest readability win SQL has to offer.
- Define a simple CTE with
WITH name AS (SELECT ...) - Chain multiple CTEs in a single
WITH - Understand that a CTE is scoped to one statement
- Write a recursive CTE with
WITH RECURSIVE - Walk an org chart or category tree with a recursive CTE
- Use CTEs for data-changing statements (
WITH ... AS ... UPDATE/DELETE)
psql -U postgres -d sql_exercise -f 04-advanced-databases/13-common-table-expressions/schema.sql
psql -U postgres -d sql_exercise -f 04-advanced-databases/13-common-table-expressions/seed.sql
psql -U postgres -d sql_exerciseInside psql:
SET search_path TO adv_cte;Use a CTE to compute the company-wide average salary, then return every employee whose salary is above it.
WITH avg_salary AS (
SELECT AVG(salary) AS avg_s FROM employee
)
SELECT e.full_name, e.salary
FROM employee e, avg_salary
WHERE e.salary > avg_salary.avg_s;Question: why is this easier to read than the equivalent sub-query version?
Write a query with two CTEs:
engineers= employees whose title contains'Engineer'engineer_avg= the average salary ofengineers
Then the main query returns every row from engineers whose salary is above engineer_avg.
Use a CTE to compute per-manager headcount, then join it back to employee to show each manager's name next to their direct-report count.
WITH direct_reports AS (
SELECT manager_id, COUNT(*) AS n FROM employee WHERE manager_id IS NOT NULL GROUP BY manager_id
)
SELECT m.full_name AS manager, dr.n AS direct_reports
FROM direct_reports dr
JOIN employee m ON m.id = dr.manager_id
ORDER BY dr.n DESC;Write a recursive CTE that starts from Ada (id = 1) and walks down the org chart, returning each employee with a level column counting how deep they are from Ada.
Hint:
WITH RECURSIVE org AS (
-- Anchor: Ada herself
SELECT id, full_name, manager_id, 1 AS level
FROM employee
WHERE id = 1
UNION ALL
-- Recursive: employees whose manager is in the CTE
SELECT e.id, e.full_name, e.manager_id, org.level + 1
FROM employee e
JOIN org ON e.manager_id = org.id
)
SELECT level, full_name FROM org ORDER BY level, full_name;Expected: Ada (1), Alan/Margaret (2), Grace/Dennis/Katherine/Radia (3), Linus/Donald (4).
Write a recursive CTE that, given an employee id (say, Linus = 4), walks up the chain of command and returns every one of their managers up to the CEO.
Expected: Linus → Grace → Alan → Ada.
For each employee, return full_name and the sum of salaries of everyone below them (direct and indirect reports). Use a recursive CTE.
Hint: build the full (boss → subordinate) transitive closure in a recursive CTE, then sum.
Write a recursive CTE that returns every category along with its full path, like Electronics > Computers > Laptops. Hint: accumulate the path into a TEXT column in the recursive step.
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, name::TEXT AS path
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, tree.path || ' > ' || c.name
FROM category c
JOIN tree ON tree.id = c.parent_id
)
SELECT id, path FROM tree ORDER BY path;Return every category beneath 'Computers' (id = 2) — direct and indirect. Use a recursive CTE.
Expected: Computers, Laptops, Desktops.
Modify the recursive CTE from Exercise 4 to only return employees at level <= 3 (Ada, her directs, and her directs' directs).
Hint: add WHERE org.level < 3 to the recursive step.
Compute the total sales per calendar month from the sale table. Use a CTE to DATE_TRUNC each sale's month once, then group.
WITH monthly AS (
SELECT DATE_TRUNC('month', sold_at)::DATE AS month, amount_cents
FROM sale
)
SELECT month, SUM(amount_cents) AS total
FROM monthly
GROUP BY month
ORDER BY month;Building on Exercise 10, return each month's total alongside the previous month's total and the difference.
You haven't seen window functions yet (that's next section), so use a self-join on the CTE:
WITH monthly AS (
SELECT DATE_TRUNC('month', sold_at)::DATE AS month, SUM(amount_cents) AS total
FROM sale
GROUP BY 1
)
SELECT m1.month, m1.total,
m2.total AS prev_month_total,
m1.total - COALESCE(m2.total, 0) AS diff
FROM monthly m1
LEFT JOIN monthly m2 ON m2.month = m1.month - INTERVAL '1 month'
ORDER BY m1.month;Use a CTE as the source of rows for an UPDATE. Give every employee whose salary is below the company average a 5% raise:
WITH low_paid AS (
SELECT id FROM employee WHERE salary < (SELECT AVG(salary) FROM employee)
)
UPDATE employee
SET salary = (salary * 1.05)::INT
WHERE id IN (SELECT id FROM low_paid);Verify: SELECT full_name, salary FROM employee ORDER BY salary;
Delete the three smallest sales and return what you deleted. Use WITH ... AS (DELETE ... RETURNING ...).
WITH doomed AS (
DELETE FROM sale
WHERE id IN (SELECT id FROM sale ORDER BY amount_cents ASC LIMIT 3)
RETURNING *
)
SELECT * FROM doomed;Use a recursive CTE (no generate_series!) to produce the numbers 1 to 10.
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;Question: what is the "anchor" part and what is the "recursive" part? What would happen if you forgot the WHERE n < 10?
Postgres has a CYCLE clause for recursive CTEs. Read \h WITH and use it on the category tree. Why is cycle detection important for user-editable category trees?
Read \h WITH. What's the difference between WITH foo AS MATERIALIZED (...) and WITH foo AS NOT MATERIALIZED (...)? When would you force MATERIALIZED?
Without window functions, compute the 3-month moving average of monthly sales using self-joins on a CTE. (Window functions will make this a one-liner in the next section.)
DROP SCHEMA IF EXISTS adv_cte CASCADE;