Multi-level parent-child nodes relationship

I have an original table with 2 columns, parent_node_id (parent) and node_id (child) and I want to get a final output of 3 columns: "root", "internal" and "leaf" nodes. I want to use group_concat() and group by to achieve the expected output in the picture provided.

This is the original table from the tree diagram in the picture provided.

question case

I need to get an output that looks like this:

I tried creating 3 CTEs to get individual root, internal and leaf nodes. However, I am unable to create a sub relational table that gives the relationship with the parent and root nodes.

Here is what i tried:

    select
	m.node_id as root,
    group_concat(distinct i.node_id order by i.node_id separator ',') as internal,
    group_concat(distinct l.node_id order by l.node_id separator ',') as leaf
from craig_test.test m
left join craig_test.test r on m.node_id = r.node_id and r.parent_node_id is null
left join craig_test.test i on m.node_id = i.node_id and i.node_id in (select parent_node_id from craig_test.test where parent_node_id is not null)
left join craig_test.test l on m.node_id = l.node_id and l.node_id not in (select parent_node_id from craig_test.test where parent_node_id is not null)
-- where m.parent_node_id is null
group by 1

There are more efficient ways of doing this, but this will provide the result you requested:

// build list of all nodes with their parent and whether they have children
WITH RECURSIVE hierarchy (root_node_id, node_id, has_children) AS (
    SELECT
        t.node_id,
        t.node_id,
        EXISTS (SELECT 1 FROM test WHERE parent_node_id = t.node_id)
	FROM test t
    WHERE t.parent_node_id IS NULL

    UNION ALL

    SELECT
        h.root_node_id,
        t.node_id,
        EXISTS (SELECT 1 FROM test WHERE parent_node_id = t.node_id)
	FROM test t
    JOIN hierarchy h ON t.parent_node_id = h.node_id
)
SELECT
    root_node_id AS root,
    GROUP_CONCAT(IF(has_children AND root_node_id <> node_id, node_id, NULL)) AS internal,
    GROUP_CONCAT(IF(NOT has_children, node_id, NULL)) AS leaf
FROM hierarchy
GROUP BY root_node_id;

The CTE builds:

And then the GROUP BY query outputs: