How to find sum of depths of subtrees using SQL query?

Question

PostgreSQL

Good afternoon.
Having trouble with a PostgreSQL tutorial problem on subtree sizing.
There is a table like this:

Keyword(id INT PRIMARY KEY, value TEXT, parent_id INT REFERENCES Keyword DEFAULT NULL);

And you need to output for each node the size of the subtree (considering the current element). For example, on the data:

Keyword                   
id  value  parent_id             
-------------------------
0   '1'       NULL      
1   '2'       0         
2   '3'       0         
3   '4'       1         
4   '5'       1

The query should return a table:

id  size
-------------
   0   5
   1   3
   2   1
   3   1
   4   1

And I'm having trouble compiling a recursive query because I don't understand how it's possible to collect all the data needed to complete the task.
With a query like this:

WITH RECURSIVE tree AS (
    SELECT id, parent_id, array[id] AS path, 0 AS level FROM Keyword 

    UNION ALL

    SELECT Child.id, Parent.id, array_append(path, Child.id) AS parent_id, Parent.level + 1 AS level
    FROM Keyword Child JOIN tree Parent ON Child.parent_id = Parent.id
)
SELECT * FROM tree

I am getting all paths from the root to the node with id at level.
Give me an idea, please.

Answer 1

The result is a recursive query that prints the sum of subtree depths for each tree node.
On each pass, we build a table containing the node id and the path from the parent. After that, for each vertex, we count how many paths contain the id of the vertex as a parent.

WITH RECURSIVE tree_depth as (
  SELECT id, array[id] AS parents, 0 AS level
  FROM Keyword

  UNION ALL

  SELECT C.id, P.parents || C.id, P.level + 1 as level FROM Keyword C
  JOIN tree_depth P ON P.id = C.parent_id
)
Select parents[1], COUNT(*) from tree_depth group by parents[1] order by parents[1];

Answer 2

Write a procedure or keep the depth next to the record