WITH Clause — Common Table Expressions

-- Stage the data, then aggregate over it
WITH recent_orders AS (
    SELECT user_id, amount
    FROM   orders
    WHERE  created_at >= NOW() - INTERVAL '30 days'
),
user_stats AS (
    SELECT user_id,
           COUNT(*)     AS order_count,
           SUM(amount)  AS total_spent
    FROM   recent_orders
    GROUP  BY user_id
)
SELECT u.email,
       s.order_count,
       s.total_spent
FROM   user_stats s
JOIN   users u ON u.id = s.user_id
WHERE  s.total_spent > 500
ORDER  BY s.total_spent DESC;

-- Multiple CTEs can reference each other (each only the ones BEFORE it).
-- The PostgreSQL optimizer can usually fold CTEs into the outer query;
-- use WITH ... AS MATERIALIZED to force materialization.