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

// 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;