Description

Login
Back

Advanced · 18 min

Common table expressions

Name an intermediate query to make a report easier to read.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Separate query stages

WITH introduces a common table expression that is visible within one statement. It is a named query result, not a permanent table. The database may inline or materialize it; do not assume that naming it guarantees a performance improvement.

WITH totals AS (SELECT member_id, SUM(minutes) AS minutes FROM sessions GROUP BY member_id) SELECT member_id, minutes FROM totals WHERE minutes >= 40 ORDER BY member_id;

Output

member_id | minutes
2 | 45
3 | 40

Combine a CTE with a join

Pre-aggregate a many-side table before joining when one row per member is the intended grain. This also makes the treatment of members with no activity explicit.

WITH totals AS (SELECT member_id, SUM(minutes) AS minutes FROM sessions GROUP BY member_id) SELECT m.name, COALESCE(t.minutes, 0) AS minutes FROM members AS m LEFT JOIN totals AS t ON t.member_id = m.id ORDER BY m.id;

Output

name | minutes
Ada | 30
Bo | 45
Cy | 40
Dee | 0

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Use a CTE to total minutes per member, then keep totals of at least 40, ordered by member ID.

Show solution
WITH totals AS (SELECT member_id, SUM(minutes) AS minutes FROM sessions GROUP BY member_id) SELECT member_id, minutes FROM totals WHERE minutes >= 40 ORDER BY member_id;

The CTE establishes the per-member totals and the outer query filters those totals.

Practice