SQL Interview Prep · Lesson

Writing Your First CTE

Basic WITH syntax and when a CTE improves readability over a subquery.

Writing Your First CTE is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a CTE Actually Is

A Common Table Expression (CTE) is a named, temporary result set defined with the WITH keyword that exists only for the duration of a single query. Interviewers love CTEs because they reveal whether you can structure logic clearly.

Think of a CTE as giving a name to a subquery so you can reference it like a table in the main statement that follows. It does not create a permanent object and disappears the moment the query finishes.

The Basic WITH Syntax

Every CTE starts with WITH, a name, the keyword AS, and a parenthesized query. After the closing parenthesis you write a normal statement that uses the CTE by name.

WITH cte_name AS ( ... ) defines the block.
The query right after the parenthesis is the main query.
The CTE name behaves like a table you can SELECT from.

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT *
FROM recent_orders;

Why Not Just Use a Subquery?

The same logic can be written as an inline subquery in the FROM clause. So why do interviewers ask about CTEs?

Readability: a named step reads top-to-bottom like a recipe.
Reuse: you can reference the same CTE multiple times instead of repeating a subquery.
Debuggability: you can SELECT just the CTE to inspect it.

The right answer in an interview is usually: use a CTE when it makes the query easier to read and maintain.

Worked Example: Filtering Then Aggregating

Suppose the question is: find the total revenue from orders placed this year. A CTE lets you isolate the filtering step, then aggregate over the named result.

The main query treats recent_orders as if it were a real table, which keeps the aggregation clean and obvious.

WITH recent_orders AS (
    SELECT amount
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT SUM(amount) AS total_revenue
FROM recent_orders;

Naming the Output Columns

You can rename the columns a CTE exposes by listing them right after the CTE name. This is handy when the inner query produces expressions or you want clearer names downstream.

If you supply a column list, it must match the number of columns the inner query returns, or the database raises an error.

WITH revenue (region, total) AS (
    SELECT region, SUM(amount)
    FROM orders
    GROUP BY region
)
SELECT region, total
FROM revenue
ORDER BY total DESC;

A CTE Is Just a Named Query

One mental model that impresses interviewers: a CTE is logically equivalent to substituting its definition inline. The database can choose to inline it or materialize it, but semantically the result is the same as if you pasted the subquery.

This means anything legal in a regular SELECT is legal inside a CTE: joins, GROUP BY, WHERE, window functions, and more.

Deeper Example: CTE With a Join

CTEs shine when you need to pre-shape one side of a join. Here we build a per-customer order count first, then join it back to the customers table so each customer row carries its total.

Note how the main query reads almost like English: from customers, join their order counts.

WITH order_counts AS (
    SELECT customer_id, COUNT(*) AS num_orders
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, oc.num_orders
FROM customers c
JOIN order_counts oc
    ON oc.customer_id = c.id;

Where a CTE Lives in the Query

The WITH block must come before the statement that uses it. The CTE is visible only to the single statement immediately following its definition.

You cannot reference a CTE in a later, separate query.
A CTE defined for a SELECT cannot be reused by a different SELECT run afterward.
Scope ends with the semicolon that terminates the statement.

CTEs Work With INSERT, UPDATE, DELETE

A frequent follow-up: CTEs are not limited to SELECT. In most modern databases you can attach a WITH clause to data-modification statements too.

This lets you compute a set of rows once and then act on them, which reads far more clearly than a nested subquery inside the WHERE clause.

WITH stale AS (
    SELECT id
    FROM sessions
    WHERE last_seen < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM stale);

Common Beginner Mistakes

Interviewers watch for these slips:

Forgetting the main query after the CTE; a WITH block alone is not a complete statement.
Putting a semicolon between the CTE and the main query.
Expecting the CTE to persist across multiple statements.
Mismatching the optional column-name list with the inner query's columns.

How to Talk About CTEs in an Interview

When asked to refactor a messy subquery, narrate your thinking: I will lift this filtering subquery into a CTE called recent_orders so the aggregation reads cleanly.

Demonstrating that you choose CTEs for clarity and reuse, not blindly, signals mid-level maturity. Mention that a CTE does not inherently make a query faster; its main value is readability.

Quick Check

Test your understanding of basic CTE syntax and scope.

Recap: Your First CTE

You learned that a CTE uses WITH name AS ( ... ) to name a temporary result set, then references it like a table in the following statement.

CTEs improve readability, reuse, and debuggability over inline subqueries.
Scope is limited to one statement; they vanish afterward.
They work with SELECT and with INSERT/UPDATE/DELETE.
They do not inherently boost performance.

Next: chaining several CTEs into a pipeline.

Frequently asked questions

Is the “Writing Your First CTE” lesson free?

Yes — the full text of “Writing Your First CTE” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Writing Your First CTE”?

Basic WITH syntax and when a CTE improves readability over a subquery. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Writing Your First CTE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep