Postgresql: more than one row returned by a subquery used as an expression?

I have a query to find the youngest generation in a family tree. This is my database

ID  NAME PARENT_ID
1   A       0
2   B       1
3   C       1
4   D       2
5   E       3
6   F       3
7   G       6

I need an output like below if result has more than one row

NAME GENERATIONS
E       4
F       4

or if the result returns single row

NAME GENERATIONS
G       5   

This is my query:

WITH RECURSIVE children AS
(
    SELECT id, name, parent_id 
    FROM family
    
    UNION ALL
    
    SELECT f.id, f.name, f.parent_id 
    FROM family f   
    INNER JOIN children c ON c.id = f.parent_id
)

SELECT 
    (
        SELECT name 
        FROM family
        WHERE id IS NOT NULL
        AND parent_id =
        (
            SELECT MAX(parent_id) as maxpi
            FROM family
        )
    ) AS name,  
        COUNT(DISTINCT main.parent_id) AS generations

FROM family main
ORDER BY name

How to fix this? And another question is there a way to get result using window funtion?

Answer

Calculate the generation as you recurse, and then get the rows that match the max(generation).

WITH RECURSIVE children AS (
    SELECT id, name, parent_id, 1 as generation
      FROM family
     WHERE parent_id not in (SELECT id FROM family)
    UNION ALL
    SELECT f.id, f.name, f.parent_id, c.generation + 1 as generation
      FROM family f   
           INNER JOIN children c ON c.id = f.parent_id
)
SELECT *
  FROM children
 WHERE generation = (select max(generation) from children);