Structuring Your Queries with CTEs (WITH)

In the previous course, you nested subqueries in the FROM clause. As soon as there are two or three levels, the query has to be read from the inside out and becomes hard to follow. CTEs (Common Table Expressions) solve this problem: you name each step and write them in the order you think of them.

Your first CTE

WITH totaux AS (                -- step 1: one total per salesperson
    SELECT vendeur, SUM(montant) AS total
    FROM ventes
    GROUP BY vendeur
)
SELECT vendeur, total           -- step 2: query step 1
FROM totaux
WHERE total >= 350
ORDER BY total DESC;

WITH totaux AS (...) defines a named intermediate result called totaux. The main query uses it like a table. The CTE is not stored in the database: it exists only while this query runs.

Chaining steps

You can define several CTEs with a single WITH, separated by commas. Each one can use the previous ones:

WITH ca_mensuel AS (
    SELECT substr(date_vente, 1, 7) AS mois,
           SUM(montant)             AS ca
    FROM ventes
    GROUP BY mois
),
total AS (
    SELECT SUM(ca) AS ca_total FROM ca_mensuel   -- uses the previous CTE
)
SELECT m.mois,
       m.ca,
       ROUND(100.0 * m.ca / t.ca_total, 1) AS part_pct
FROM ca_mensuel AS m
CROSS JOIN total AS t                             -- total has only one row
ORDER BY m.mois;

Each block answers a small question; the final query assembles the answers. This is the safest way to write a complex analysis: you can test each CTE separately by replacing the final query with SELECT * FROM ca_mensuel.

Reusing a CTE

A CTE can be referenced several times. Example: keep the salespeople above the average of salespeople.

WITH totaux AS (
    SELECT vendeur, SUM(montant) AS total
    FROM ventes
    GROUP BY vendeur
)
SELECT vendeur, total
FROM totaux
WHERE total >= (SELECT AVG(total) FROM totaux);

Without a CTE, you would have to write the same aggregation subquery twice, with the risk of the two diverging when one is modified.

What about performance?

A CTE gives the same result as an equivalent subquery. As for performance, PostgreSQL (since version 12) generally inlines a simple CTE into the main query, like a subquery; you can force a single computation with WITH totaux AS MATERIALIZED (...) or the opposite with NOT MATERIALIZED. SQLite applies comparable logic. In practice: write for readability first, then measure.

CTE, subquery or view?

Tool	When to use it
Subquery	Short calculation used once (`WHERE prix > (SELECT AVG(prix) ...)`)
CTE	Multi-step analysis, step reused within the same query
View (`CREATE VIEW`)	Logic reused by several queries or several people

CTEs are also the gateway to recursive CTEs, the subject of the next lesson, and they combine very well with window functions.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

CodeDifficulty 2SQL (SQLite) in the browser1 hint

Salespeople above average

Available table: ventes(id, date_vente, region, vendeur, montant), 9 sales from January to March 2026.

Using a CTE totaux (one total per salesperson), display the vendeur and their total for the salespeople whose total is greater than or equal to the average of the salespeople’s totals.

Exercise data (SQL)

CREATE TABLE ventes (
  id INTEGER PRIMARY KEY,
  date_vente TEXT NOT NULL,     -- AAAA-MM-JJ
  region TEXT NOT NULL,
  vendeur TEXT NOT NULL,
  montant INTEGER NOT NULL
);
INSERT INTO ventes VALUES
 (1, '2026-01-05', 'Nord', 'Alice', 200),
 (2, '2026-01-18', 'Nord', 'Bruno', 150),
 (3, '2026-01-25', 'Sud', 'Chloé', 300),
 (4, '2026-02-03', 'Nord', 'Alice', 100),
 (5, '2026-02-10', 'Sud', 'Chloé', 100),
 (6, '2026-02-21', 'Sud', 'Damien', 250),
 (7, '2026-03-02', 'Nord', 'Bruno', 150),
 (8, '2026-03-14', 'Sud', 'Damien', 150),
 (9, '2026-03-28', 'Nord', 'Alice', 50);

WITH totaux AS (
    ...
)
SELECT vendeur, total
FROM totaux
WHERE ...;

CodeDifficulty 3SQL (SQLite) in the browser2 hints

Each month's share

Available table: ventes(id, date_vente, region, vendeur, montant), 9 sales from January to March 2026.

Using two CTEs (ca_mensuel then total), display for each month (YYYY-MM, alias mois) the revenue (alias ca) and its share of the total as a percentage rounded to 1 decimal place (alias part_pct). Sort by month.

Exercise data (SQL)

CREATE TABLE ventes (
  id INTEGER PRIMARY KEY,
  date_vente TEXT NOT NULL,     -- AAAA-MM-JJ
  region TEXT NOT NULL,
  vendeur TEXT NOT NULL,
  montant INTEGER NOT NULL
);
INSERT INTO ventes VALUES
 (1, '2026-01-05', 'Nord', 'Alice', 200),
 (2, '2026-01-18', 'Nord', 'Bruno', 150),
 (3, '2026-01-25', 'Sud', 'Chloé', 300),
 (4, '2026-02-03', 'Nord', 'Alice', 100),
 (5, '2026-02-10', 'Sud', 'Chloé', 100),
 (6, '2026-02-21', 'Sud', 'Damien', 250),
 (7, '2026-03-02', 'Nord', 'Bruno', 150),
 (8, '2026-03-14', 'Sud', 'Damien', 150),
 (9, '2026-03-28', 'Nord', 'Alice', 50);

WITH ca_mensuel AS (
    ...
),
total AS (
    ...
)
SELECT ...
FROM ca_mensuel CROSS JOIN total
ORDER BY mois;

Multiple choiceDifficulty 11 hint
Scope of a CTE
You run WITH t AS (SELECT 1 AS x) SELECT * FROM t; and then, in a second query, SELECT * FROM t;. What happens?
- The second query returns 1
- The second query fails: t exists only in the first one
- The second query returns an empty table

End-of-lesson quiz

How do you define two CTEs in the same query?
- WITH a AS (...) WITH b AS (...)
- WITH a AS (...), b AS (...)
- WITH a, b AS (...)
Can a CTE use a CTE defined before it in the same WITH?
- Yes
- No
- Only in PostgreSQL
What is the main benefit of a CTE over an equivalent subquery?
- It is always faster
- It makes the query more readable and reusable within the same query
- It saves the result in the database

The rest of the course

Recursive CTEs: Series and Hierarchies18 min
Window Functions: OVER, PARTITION BY and Rankings20 min
Running Totals and Moving Averages: ORDER BY and Window Frames18 min
Comparing Over Time: LAG, LEAD and NTILE20 min

Build my path See the full syllabus

Advanced SQL (CTEs, window functions) · Advanced · 2 h 47 min

Objectives