Level 16: Structuring with WITH (CTE)

Lesson

When a query chains several calculations, subqueries nest inside each other and reading becomes hard: you have to read from the inside out. WITH lets you write the same steps from top to bottom, like a recipe.

WITH name AS (SELECT …) creates a named temporary table, which the query that follows can use like a real table. This is called a CTE (Common Table Expression). It only exists for the duration of the query: nothing is saved in the database.

Example: WITH totals AS (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) SELECT seller, total FROM totals WHERE total > 1200. The first step computes the total of each seller; the second filters that result with a plain WHERE, since total is now an ordinary column of totals.

You can chain several CTEs, separated by commas, without repeating WITH: WITH a AS (…), b AS (… FROM a …) SELECT … FROM b. Each CTE can use the ones before it. The final query comes after the last one, without a comma.

A CTE can be read several times in the same query. A monthly CTE that computes the total of each month can be used both in the FROM and in a subquery that computes the average of those totals, without rewriting the calculation.

Method tip: build one CTE at a time. Write the first step, run it on its own and check its result, then add the next step. If the final result is wrong, you know which step to look at.

Mind the punctuation: no semicolon between a CTE and the rest of the query, it would cut it in two.

Syntax

WITH step1 AS (
  SELECT …
),
step2 AS (
  SELECT …
  FROM step1
)
SELECT …
FROM step2;

Worked example

WITH totals AS (
  SELECT seller, SUM(amount) AS total
  FROM sales
  GROUP BY seller
)
SELECT seller, total
FROM totals
WHERE total > 1200;

Step 1: the total per seller. Step 2: we filter that result like an ordinary table.

Example result

Key points

• WITH name AS (query) names a step.
• Several CTEs are separated by commas, without repeating WITH.
• The final query comes after the last CTE.

Common pitfalls

• Putting a semicolon after the CTE's parenthesis cuts the query in two.
• A CTE only exists for the duration of the query.

The level’s 5 exercises

1. Guided · With a CTE it that contains the employees of the 'IT' department, show the name and hiring date of those hired after 2018 (from January 1st, 2019). (Table: staff)
2. Practice · With a CTE, compute the average of each student (student_id), then show the students whose average is at least 13, rounded to 2 decimal places (avg_grade). (Table: grades)
3. Practice · With a CTE done that computes the hours of the finished tasks (status = 'done') per project, show the name of each project and those hours. (Tables: tasks, projects)
4. Practice · With a CTE monthly that computes the total sales of each month, show the months whose total is above the average of the monthly totals. (Table: sales)
5. Challenge · Compute the balance of each account (sum of its transactions), then the total wealth of each owner (owner), from richest to poorest. Accounts without transactions are ignored. (Tables: transactions, accounts)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic