Using Common Table Expressions

WITH high_value AS (
   SELECT customer_id, sum(total_cents) AS total
     FROM orders GROUP BY customer_id HAVING sum(total_cents) > 100000
)
SELECT c.full_name, h.total FROM high_value h JOIN customers c ON c.id = h.customer_id;

WITH
   recent AS (SELECT * FROM orders WHERE placed_at >= now()-interval '30 days'),
   per_customer AS (SELECT customer_id, count(*) AS n FROM recent GROUP BY customer_id)
SELECT * FROM per_customer ORDER BY n DESC;

WITH RECURSIVE numbers(n) AS (
   SELECT 1
   UNION ALL
   SELECT n+1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

WITH RECURSIVE org AS (
   SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS path
     FROM employees WHERE manager_id IS NULL
   UNION ALL
   SELECT e.id, e.name, e.manager_id, o.depth+1, o.path || e.id
     FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY path;

WITH RECURSIVE reachable AS (
   SELECT src, dst FROM edges WHERE src = :start
   UNION
   SELECT r.src, e.dst
     FROM reachable r JOIN edges e ON e.src = r.dst
)
SELECT DISTINCT dst FROM reachable;

WITH new_rows AS (
   INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999)
   RETURNING *
)
INSERT INTO audit (order_id, action) SELECT id, 'created' FROM new_rows;

WITH updated AS (
   UPDATE orders SET status='shipped'
    WHERE status='paid' AND placed_at < now()-interval '1 day'
   RETURNING id
)
SELECT count(*) FROM updated;

WITH purged AS (
   DELETE FROM events WHERE created_at < now()-interval '90 days' RETURNING id
)
INSERT INTO purged_log SELECT id, now() FROM purged;

WITH q AS MATERIALIZED (
   SELECT * FROM big_table WHERE expensive_predicate
)
SELECT * FROM q WHERE q.x = 1 UNION ALL SELECT * FROM q WHERE q.x = 2;

WITH q AS NOT MATERIALIZED (SELECT id FROM big WHERE active)
SELECT * FROM other JOIN q USING (id);     -- inlined for predicate push-down

WITH RECURSIVE tree AS (
   SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL
   UNION ALL
   SELECT n.id, n.parent_id, n.name FROM nodes n JOIN tree t ON n.parent_id = t.id
) SEARCH DEPTH FIRST BY id SET ord
SELECT * FROM tree ORDER BY ord;

WITH RECURSIVE walk(node, path, is_cycle) AS (
   SELECT id, ARRAY[id], false FROM nodes WHERE id = 1
   UNION ALL
   SELECT n.id, w.path || n.id, n.id = ANY(w.path)
     FROM nodes n JOIN walk w ON n.parent_id = w.node
    WHERE NOT w.is_cycle
) CYCLE node SET is_cycle USING path
SELECT * FROM walk;