SQLite: работа с деревом id-pid

WITH воистину творит чудеса. Яркий пример

CREATE TABLE test (
  id INTEGER PRIMARY KEY,
  pid INTEGER,
  name VARCHAR(50)
);

INSERT INTO test (id,pid,name) VALUES (1,NULL,'Дед');
INSERT INTO test (id,pid,name) VALUES (2,NULL,'Баба');
INSERT INTO test (id,pid,name) VALUES (3,NULL,'Тетя Ира');
INSERT INTO test (id,pid,name) VALUES (4,1,'Папа');
INSERT INTO test (id,pid,name) VALUES (5,2,'Мама');
INSERT INTO test (id,pid,name) VALUES (6,4,'Жанна');
INSERT INTO test (id,pid,name) VALUES (7,5,'Полина');
INSERT INTO test (id,pid,name) VALUES (8,NULL,'Эльза');

-- Показ дерева
WITH RECURSIVE
aaa (level, id, pid, name) AS (
    SELECT
        0 as level,
        id,
        pid,
        name
    FROM test WHERE pid IS NULL
    UNION
    SELECT
        level+1,
        bbb.id,
        bbb.pid,
        bbb.name
    FROM
        test bbb INNER JOIN aaa ON aaa.id=bbb.pid
    ORDER BY 1 DESC
)
SELECT substr('..........',1,level*3) || name FROM aaa;


-- Выбор всех самых длинных ветвей
WITH RECURSIVE
aaa (level, rootid, id, pid, name) AS (
    SELECT
        0 as level,
        id as rootid,
        id,
        pid,
        name
    FROM test WHERE pid IS NULL
    UNION
    SELECT
        level+1,
        rootid,
        bbb.id,
        bbb.pid,
        bbb.name
    FROM
        test bbb INNER JOIN aaa ON aaa.id=bbb.pid
    ORDER BY 1 DESC
)
SELECT rootid, group_concat(name) FROM aaa GROUP BY 1;

-- Выбор конечных ветвей
WITH RECURSIVE
aaa (level, rootid, id, pid, name) AS (
    SELECT
        0 as level,
        id as rootid,
        id,
        pid,
        name
    FROM test WHERE pid IS NULL
    UNION
    SELECT
        level+1,
        rootid,
        bbb.id,
        bbb.pid,
        bbb.name
    FROM
        test bbb INNER JOIN aaa ON aaa.id=bbb.pid
)
SELECT id, name FROM aaa X
    INNER JOIN (
       SELECT rootid, max(level) as maxlevel FROM aaa GROUP BY rootid
    ) Y
ON X.rootid=Y.rootid and X.level=Y.maxlevel;