What is a CTE — Basic Syntax and Mental Model

A Common Table Expression (CTE) is a named, temporary result set that you define at the top of a query and reference as if it were a table. It exists only for the duration of the query. You've already used CTEs throughout the Window Functions section — this lesson builds a complete mental model and covers every syntactic detail.

The basic structure

WITH cte_name AS (
    SELECT ...   -- the CTE query
)
SELECT ...       -- the main query that uses the CTE
FROM cte_name;

The WITH keyword introduces the CTE. cte_name is the name you give it. The main query below can reference cte_name anywhere a table name would appear: in FROM, JOIN, WHERE subqueries, and so on.

A concrete example — without CTE first

Find ec_products where revenue is above the average product revenue:

-- Without CTE: nested subquery, computed twice
SELECT
    p.name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM ec_order_items oi
JOIN ec_products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name, p.category
HAVING SUM(oi.quantity * oi.unit_price) > (
    SELECT AVG(product_rev)
    FROM (
        SELECT SUM(quantity * unit_price) AS product_rev
        FROM ec_order_items
        GROUP BY product_id
    ) sub
)
ORDER BY revenue DESC;

This works but is hard to read — the average is buried in a nested subquery, and you have to trace two levels of nesting to understand the logic.

-- With CTE: each concept named, easy to follow
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM ec_order_items oi
    JOIN ec_products p ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category
),
avg_revenue AS (
    SELECT AVG(revenue) AS avg_rev FROM product_revenue
)
SELECT
    pr.name,
    pr.category,
    pr.revenue,
    ROUND(ar.avg_rev, 2) AS avg_product_rev,
    ROUND(pr.revenue - ar.avg_rev, 2) AS above_avg_by
FROM product_revenue pr
CROSS JOIN avg_revenue ar
WHERE pr.revenue > ar.avg_rev
ORDER BY pr.revenue DESC;

Same result. The logic reads top-to-bottom like a recipe: first compute revenue per product, then compute the average, then filter and compare.

Purchase this course to unlock the full lesson.

← Previous Next →