A CTE (Common Table Expression), introduced by WITH, names an intermediate result that can be used in the main query like a temporary table. It breaks a complex query into readable steps and can be referenced several times. It exists only for the duration of the query.
Example
WITH ca AS (
SELECT client_id, SUM(total) AS total FROM commandes GROUP BY client_id
)
SELECT * FROM ca WHERE total > 1000;