CTE

A CTE gives a name to an intermediate result

A Common Table Expression is defined with WITH. It lets you move an intermediate query to the top, give it a meaningful name, and then refer to it like a temporary table within that SQL statement.

WITH paid_orders AS (
    SELECT id, user_id, amount
    FROM orders
    WHERE status = 'PAID'
)
SELECT user_id, SUM(amount) AS total
FROM paid_orders
GROUP BY user_id;

First, the paid_orders set is defined, then the main query works with that named result. The logic is not necessarily different, but the structure of a complex query often becomes much easier to understand.

A CTE exists only within one statement

A CTE does not create a permanent database table. Its name is available only to the statement that follows the WITH block.