Learn SQL: WITH (CTE)
Readable with WITH
Sanne's questions keep getting longer: subquery in subquery in subquery. "Can't that be tidier?" It can: you give each intermediate result a name.
An intermediate result with a name
With WITH name AS (query) you make a temporary result with a name, a CTE (common table expression). After that you use the name like a table.
Here per_member counts the loans per member. The real query links the names to it.
WITH per_member AS (SELECT member_id, COUNT(*) AS total FROM loans GROUP BY member_id) SELECT TRIM(m.name) AS name, p.total FROM per_member p JOIN members m ON p.member_id = m.id ORDER BY p.total DESC| name | total |
|---|---|
| Sanne Willems | 3 |
| Amira El Idrissi | 3 |
| Nina De Clercq | 3 |
| Marie Dewulf | 3 |
| Jef Wouters | 2 |
| Pieter Desmet | 2 |
| Lotte Vandamme | 2 |
| Hamid Benali | 2 |
Several CTEs
You can make several CTEs, separated by commas, and a CTE can use the previous one. Each step gets a name that says what it does, and you read the query from top to bottom.
A CTE only exists during that one query. Nothing is stored.
WITH per_member AS (SELECT member_id, COUNT(*) AS total FROM loans GROUP BY member_id), late AS (SELECT member_id, COUNT(*) AS times FROM loans WHERE returned_on > due_on GROUP BY member_id) SELECT p.member_id, p.total, t.times AS late FROM per_member p LEFT JOIN late t ON t.member_id = p.member_id| member_id | total | late |
|---|---|---|
| 1 | 3 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 2 |
| 4 | 2 | NULL |
| 5 | 2 | NULL |
| 7 | 3 | 2 |
| 8 | 2 | 1 |
| 9 | 2 | NULL |
Try it yourself
Which members borrowed more books than average? Show member_id and their number of loans.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson