SQL CTEs Practice Exercises

What is a CTE?

A CTE (Common Table Expression) is a named, temporary result set defined with a WITH clause at the start of a query. It exists only for the duration of that query, and the main SELECT can reference it just like a regular table.

-- Define a CTE, then select from it
WITH high_value_orders AS (
    SELECT *
    FROM online_bookstore_orders
    WHERE price_usd > 30
)
SELECT *
FROM high_value_orders
WHERE shipping_method = 'Express';

The CTE high_value_orders runs first and produces a result set. The outer query then treats it as a normal table to filter or select from.

Using a CTE to filter on an aggregate

Just like a subquery, a CTE is useful when you need to compare each row against a calculated value — since aggregates like AVG can't be used directly in a WHERE clause. Put the aggregate in the CTE, then reference it in the main query.

-- Orders priced above the overall average
WITH avg_price AS (
    SELECT AVG(price_usd) AS avg_price_usd
    FROM online_bookstore_orders
)
SELECT o.order_id, o.customer_id, o.price_usd
FROM online_bookstore_orders o
CROSS JOIN avg_price a
WHERE o.price_usd > a.avg_price_usd;

Because the CTE returns exactly one row, a CROSS JOIN simply attaches that single average value onto every row of the outer table so it can be compared.

Filtering within a group

A CTE's inner query can include its own WHERE clause, so you can calculate an aggregate for just one category or group, then compare the full table against it.

-- Bookings longer than the average Suite stay
WITH avg_suite_stay AS (
    SELECT AVG(nights_stayed) AS avg_nights
    FROM hotel_room_bookings
    WHERE room_type = 'Suite'
)
SELECT b.*
FROM hotel_room_bookings b
CROSS JOIN avg_suite_stay a
WHERE b.nights_stayed > a.avg_nights;

Chaining multiple CTEs

You can define more than one CTE in the same WITH clause, separated by commas — and a later CTE can reference an earlier one. This breaks a complex query into clear, named steps.

WITH genre_avg AS (
    SELECT book_genre, AVG(price_usd) AS avg_price
    FROM online_bookstore_orders
    GROUP BY book_genre
),
mystery_avg AS (
    SELECT avg_price
    FROM genre_avg
    WHERE book_genre = 'Mystery'
)
SELECT o.*
FROM online_bookstore_orders o
CROSS JOIN mystery_avg m
WHERE o.price_usd > m.avg_price;

Common mistakes

Forgetting the CTE only lives for one query — a CTE isn't saved like a view or a table. It disappears once the query finishes and has to be redefined each time.

Using AND instead of a JOIN condition — when joining a multi-row CTE to a table, you still need a proper join condition on matching columns, not just a CROSS JOIN, which is only safe when the CTE returns a single row.

Missing commas between multiple CTEs — when chaining CTEs in one WITH clause, each definition except the last needs a comma after its closing parenthesis.

Reaching for a CTE when a plain subquery is simpler — CTEs shine when a value is reused or when breaking a query into named steps improves readability. For a single, one-off calculation, a subquery is often just as clear and shorter.