Intermediate10 min read PostgreSQL

Common Table Expressions (CTEs)

Learn how CTEs simplify complex queries, improve readability, and enable recursive data traversal.

WITH
CTE
subqueries
readability

Want the one-page version first? Read SQL CTE — The WITH Clause Explained, then come back here for the full lesson.

Use this when you need to:

Refactor a deeply nested subquery so a teammate can actually review it
Compute an intermediate aggregate you need to filter on (top customers by spend, then their churn risk)
Walk a hierarchy or graph with WITH RECURSIVE — org charts, threaded comments, category trees
Reuse the same intermediate result twice in one query (above-average / below-average filtering)

A CTE, or common table expression, is a temporary, named result set in SQL that allows you to simplify complex queries, making them easier to read and maintain. CTEs are created with the WITH keyword and can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Basic Syntax

WITH cte_name (column1, column2) AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

The WITH keyword initiates the CTE definition. You give it a name (used to reference it later), optionally list column names, and write the inner query that defines its result set.

Step-by-Step Example

Suppose you want the applications that arrived with a high-school GPA of 3.5 or better. Without a CTE, you'd write a simple SELECT. Wrapping it in a CTE names the result set and lets you reference it like a table:

WITH high_gpa_applications AS (
    SELECT applications_id, appl_applicant, appl_acad_program_title, appl_hs_gpa
    FROM ods_applications
    WHERE appl_hs_gpa >= 3.5
)
SELECT applications_id, appl_acad_program_title, appl_hs_gpa
FROM high_gpa_applications;

Why Use CTEs?

CTEs shine when a query has multiple steps. Without a CTE, joins, filters and aggregates pile into one block. Compare these two approaches for finding students who attempted more than 20 credit hours across the terms that started in 2025:

-- Without CTE: everything in one block, hard to read
SELECT p.first_name, p.last_name, SUM(scs.scs_credits) AS credits_2025
FROM ods_stu_course_sec scs
JOIN ods_person p ON p.id       = scs.scs_student
JOIN ods_terms  t ON t.terms_id = scs.scs_term
WHERE EXTRACT(YEAR FROM t.term_start_date) = 2025
GROUP BY p.id, p.first_name, p.last_name
HAVING SUM(scs.scs_credits) > 20;

-- With CTE: each step is named and readable
WITH registrations_2025 AS (
    -- one row per course registration in a term that started in 2025
    SELECT scs.scs_student, p.first_name, p.last_name, scs.scs_credits
    FROM ods_stu_course_sec scs
    JOIN ods_person p ON p.id       = scs.scs_student
    JOIN ods_terms  t ON t.terms_id = scs.scs_term
    WHERE EXTRACT(YEAR FROM t.term_start_date) = 2025
)
SELECT first_name, last_name, SUM(scs_credits) AS credits_2025
FROM registrations_2025
GROUP BY scs_student, first_name, last_name   -- group by the id: two students can share a name
HAVING SUM(scs_credits) > 20;

Multiple CTEs in One Query

You can chain multiple CTEs separated by commas. Each CTE can reference those defined before it. This is powerful for multi-stage data processing:

WITH course_registrations AS (
    -- step 1: registrations per course, across every term
    SELECT scs_course_name, COUNT(*) AS registrations
    FROM ods_stu_course_sec
    GROUP BY scs_course_name
),
average_registrations AS (
    -- step 2: one number, built from step 1
    SELECT AVG(registrations) AS avg_registrations
    FROM course_registrations
),
high_demand_courses AS (
    -- step 3: keep the courses above that average
    SELECT cr.scs_course_name, cr.registrations
    FROM course_registrations cr
    CROSS JOIN average_registrations ar
    WHERE cr.registrations > ar.avg_registrations
)
SELECT scs_course_name, registrations,
       RANK() OVER (ORDER BY registrations DESC) AS demand_rank
FROM high_demand_courses;

In PostgreSQL, CTEs are not automatically materialized — the query optimizer may inline them like subqueries. Use CTEs primarily for readability. Performance gains are situational.

Recursive CTEs

A recursive CTE references itself within its definition. This makes it ideal for hierarchical or chained data: org charts, parent-child relationships, a sequence of events. It has two parts: an anchor member (base case) and a recursive member that joins back to the CTE. In the practice schema, ods_appl_status_history is a chain: every application has one row per status it passed through, where pos = 1 is the current status and each higher pos is one step older. Walking that chain from the oldest status to the newest turns it into a readable path.

WITH RECURSIVE status_path AS (
    -- Anchor: each application's first status (no older row behind it)
    SELECT h.applications_id, h.pos,
           h.application_status_desc::text AS path,
           1 AS steps
    FROM ods_appl_status_history h
    WHERE NOT EXISTS (
        SELECT 1
        FROM ods_appl_status_history older
        WHERE older.applications_id = h.applications_id
          AND older.pos = h.pos + 1
    )

    UNION ALL

    -- Recursive: step to the next-newer status and append it to the path.
    -- INNER JOIN is required here: PostgreSQL does not allow the recursive
    -- reference on the nullable side of an outer join.
    SELECT h.applications_id, h.pos,
           sp.path || ' → ' || h.application_status_desc,
           sp.steps + 1
    FROM ods_appl_status_history h
    INNER JOIN status_path sp
            ON h.applications_id = sp.applications_id
           AND h.pos = sp.pos - 1
)
-- pos = 1 is the current status, so those rows hold each complete path
SELECT path, steps, COUNT(*) AS applications
FROM status_path
WHERE pos = 1
GROUP BY path, steps
ORDER BY applications DESC, path;

-- path                                   | steps | applications
-- Applied → Accepted → Deposited         |     3 |           70
-- Applied → Accepted                     |     2 |           64
-- Applied → Accepted → Move to Student   |     3 |           20
-- Applied → Denied                       |     2 |           20
-- New                                    |     1 |           16
-- Applied → Accepted → Withdrawn         |     3 |           10

The recursion continues until the recursive member returns no new rows: here, when it reaches pos = 1 and there is no pos 0 to step to. Be careful — an infinite loop occurs if your termination condition is never met.

CTEs with University Data

In the QueryU challenges, CTEs are especially useful for multi-step enrollment and retention analysis. For example: the first CTE picks the students registered in a term, the second totals their credit hours, and the final query calculates headcount and FTE. Breaking this into named steps makes each part testable and readable: you can run any CTE on its own to check it.

WITH active_students AS (
    -- registered in the term: leaves and withdrawals are not headcount
    SELECT sttr_student, sttr_term
    FROM ods_student_terms
    WHERE sttr_current_status = 'R'
      AND sttr_term = '2026SP'
),
credit_summary AS (
    -- LEFT JOIN keeps a registered student with no course rows (0 credits)
    SELECT a.sttr_student, COALESCE(SUM(scs.scs_credits), 0) AS total_credits
    FROM active_students a
    LEFT JOIN ods_stu_course_sec scs
           ON scs.scs_student = a.sttr_student
          AND scs.scs_term    = a.sttr_term
    GROUP BY a.sttr_student
)
SELECT
    COUNT(*)                            AS headcount,
    ROUND(SUM(total_credits) / 15.0, 1) AS fte,
    ROUND(AVG(total_credits), 2)        AS avg_credits
FROM credit_summary;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons