SQL CTEs cheat sheet

WITH completed AS (
  SELECT * FROM orders WHERE status = 'completed'
)
SELECT customer_id, COUNT(*) FROM completed GROUP BY customer_id;

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
), top AS (
  SELECT * FROM per_customer WHERE n >= 2
)
SELECT c.name, t.n FROM top t JOIN customers c ON c.id = t.customer_id;

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

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;