Common table expressions

Name each stage of a query

Lesson 14 of 20 · 13 min · Module 5: Explain a more complex report

A report is easier to reason about when its intermediate result has a name. A common table expression makes that stage explicit within one statement.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

  • Split aggregation and presentation into named stages
  • Understand the lifetime of a CTE

The idea

Use WITH name AS (...) before the main SELECT. The CTE is available to that statement; it does not create a permanently stored table.

WITH totals AS (
  SELECT learner_id, SUM(completed) AS lessons
  FROM enrollments GROUP BY learner_id
)
SELECT learner_id, lessons FROM totals;

01Write the inner result’s shape

course_totals has one row per course_id. Its lessons column is already an aggregate, so the next stage can treat it as an ordinary result column.

02Add meaning at the next stage

The courses join supplies the title without mixing it into every detail of the aggregation. Naming the stages helps you inspect each responsibility.

03Keep scope in mind

The CTE name is available only to its statement. A later independent SELECT cannot read it as if you had created a permanent table.

Your turn

Define a CTE named course_totals that returns course_id and SUM(completed) AS lessons. Join it to courses, then return title and lessons for totals of at least 5. Sort lessons descending, then title.

What the checks look for

  • Returns SQL and Python courses with at least five completed lessons
Try it in the editor →

Take this with you

A named intermediate result can make a complicated question easier to explain.

Go a little further

Add a second CTE that calculates the overall average course total, then compare each course against it.

Learn SQL Foundations for free

20 short lessons with real code and instant checks.

See the whole course →