# Walk a parent-child hierarchy

Model / graph

A variant of [Analyse a network](https://meridian.online/operations/analyse-a-network).

Follow parent-child links to build ancestor paths, a closure table of every ancestor and descendant, or totals rolled up each level.

Page: <https://meridian.online/operations/analyse-a-network/walk-a-parent-child-hierarchy>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

build ancestor paths, a closure table and rolled-up totals from parent-child links

```sql
-- an organisation as parent links: two roots, and nodes 7 and 8 that name each other as parent (a loop no root reaches)
CREATE TEMP TABLE nodes AS SELECT * FROM (VALUES
  (1, NULL, 'company', 10), (2, 1, 'sales', 5), (3, 1, 'support', 7), (4, 2, 'emea', 3), (5, 2, 'apac', 2), (6, 4, 'uk', 1), (9, NULL, 'foundation', 4), (7, 8, 'loop a', 100), (8, 7, 'loop b', 100)) t(id, parent, name, amount);
-- ancestor paths: walk down from the roots, adding each node's name to its parent's path
CREATE TEMP TABLE paths AS
WITH RECURSIVE walk(id, path, depth) AS (
  SELECT id, name, 0 FROM nodes WHERE parent IS NULL
  UNION ALL
  SELECT n.id, w.path || ' > ' || n.name, w.depth + 1 FROM walk w JOIN nodes n ON n.parent = w.id)
SELECT * FROM walk;
-- closure table: one row for every node and each of its ancestors, with the distance between them (a node is its own ancestor at distance 0)
CREATE TEMP TABLE closure AS
WITH RECURSIVE up(descendant, ancestor, distance) AS (
  SELECT id, id, 0 FROM nodes WHERE id IN (SELECT id FROM paths)
  UNION ALL
  SELECT u.descendant, n.parent, u.distance + 1 FROM up u JOIN nodes n ON n.id = u.ancestor WHERE n.parent IS NOT NULL)
SELECT * FROM up;
-- roll up: each node's own amount plus those of everything beneath it, from the closure table
CREATE TEMP TABLE rolled AS
SELECT c.ancestor AS id, sum(n.amount) AS total, count(*) - 1 AS below FROM closure c JOIN nodes n ON n.id = c.descendant GROUP BY c.ancestor;
SELECT * FROM paths ORDER BY id;
SELECT p.id, n.name, r.total, r.below FROM rolled r JOIN nodes n USING (id) JOIN paths p USING (id) ORDER BY id;
-- the loop at nodes 7 and 8 has no root above it, so the walk from the roots never reaches it and cannot run on forever
SELECT CASE WHEN ((SELECT count(*) = 7 FROM paths)
  AND (SELECT path = 'company > sales > emea > uk' AND depth = 3 FROM paths WHERE id = 6)
  AND (SELECT path = 'foundation' AND depth = 0 FROM paths WHERE id = 9)
  AND (SELECT count(*) = 16 FROM closure)
  AND (SELECT list(ancestor ORDER BY distance) = [6, 4, 2, 1] FROM closure WHERE descendant = 6)
  AND (SELECT map_from_entries(list((id, total) ORDER BY id)) = MAP {1: 28, 2: 11, 3: 7, 4: 4, 5: 2, 6: 1, 9: 4} FROM rolled)
  AND (SELECT map_from_entries(list((id, below) ORDER BY id)) = MAP {1: 5, 2: 3, 3: 0, 4: 1, 5: 0, 6: 0, 9: 0} FROM rolled)
  AND (SELECT count(*) = 0 FROM closure WHERE descendant IN (7, 8))) THEN 'as expected' ELSE error('the walk should give 7 paths, the closure table 16 rows, the company a total of 28 over 5 descendants, and nothing for the loop') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- EasyMorph: [`Hierarchy path`](https://help.easymorph.com/doku.php?id=transformations:hierarchypath) (read 2026-09-27)
- EasyMorph: [`Roll up`](https://help.easymorph.com/doku.php?id=transformations:rollup) (read 2026-09-27)
- Apache Hop: [`Closure`](https://hop.apache.org/manual/latest/pipeline/transforms/closure.html) (read 2026-09-27)
- Pentaho Data Integration: [`Closure Generator`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)