SQL Interview Prep · Lesson

Writing Your First CTE

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

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;

All lessons in this course

← Back to SQL Interview Prep