CTEs (WITH) and recursion

A CTE (Common Table Expression) is a named subquery, declared before the main SELECT. It does not change the result; it changes who can read the query. Each step becomes a named rung.

WITH delivered AS (
  SELECT * FROM orders WHERE status = 'delivered'
),
per_customer AS (
  SELECT customer_id, SUM(amount) AS total FROM delivered GROUP BY customer_id
)
SELECT c.name, p.total
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
ORDER BY p.total DESC, c.name;

name  | total
----- | -----
Ana   | 1130
Bruno | 1050
Diego | 1050
Carla | 180
Elisa | 30

WITH RECURSIVE: a calendar

The recursive part runs until it produces no new row. It is the classic way to generate the date table that "fills the gaps" in a report: here, days without sales show up with zero.

WITH RECURSIVE days(day) AS (
  SELECT '2024-06-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2024-06-07'
)
SELECT d.day, COALESCE(SUM(o.amount), 0) AS sold
FROM days d
LEFT JOIN orders o ON o.order_date = d.day AND o.status = 'delivered'
GROUP BY d.day
ORDER BY d.day;

day        | sold
---------- | ----
2024-06-01 | 1130
2024-06-02 | 180
2024-06-03 | 1050
2024-06-04 | 180
2024-06-05 | 980
2024-06-06 | 0
2024-06-07 | 0

Without the stopping condition (WHERE day < …) the recursion never ends. On the site, the query is interrupted after a few seconds; on a production database, it holds resources until someone kills it.

Hierarchies

WITH RECURSIVE team(id, name, manager, level) AS (
  SELECT 1, 'Management', NULL, 0
  UNION ALL
  SELECT t.id, t.name, t.manager, e.level + 1
  FROM (SELECT 2 AS id, 'Data' AS name, 1 AS manager UNION ALL SELECT 3, 'Engineering', 2 UNION ALL SELECT 4, 'BI', 2) t
  JOIN team e ON t.manager = e.id
)
SELECT substr('        ', 1, level * 2) || name AS tree, level FROM team ORDER BY id;

tree            | level
--------------- | -----
Management      | 0
  Data          | 1
    Engineering | 2
    BI          | 2

• In Spark SQL, a CTE is not materialized by default: if it is referenced twice, it may be computed twice. To reuse an expensive result, write a table or use cache (see Cache and persist).
• WITH RECURSIVE only reached Spark SQL and Databricks in recent versions: check the documentation for your version before counting on it. Without it, a hierarchy is solved with a loop of JOINs in PySpark.