MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `test_pages` (
  `id` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `version_id` int unsigned NOT NULL,
  `parent_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `default_page_id` char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`version_id`),
  KEY `version_id` (`version_id`),
  KEY `parent_id` (`parent_id`,`version_id`),
  CONSTRAINT `pages_fk_1` FOREIGN KEY (`parent_id`, `version_id`) REFERENCES `test_pages` (`id`, `version_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
--  Records of `test_pages`
-- ----------------------------
BEGIN;
INSERT INTO `test_pages` VALUES 
('abc1212312efff', '2', null, null, 'root', 'Root Page', null, null), 
('ejejejejejej', '2', 'abc1212312efff', null, 'container', 'Child Page 1', '2022-02-03 23:26:01', null), 
('jf2039jf932032f', '2', 'abc1212312efff', null, 'container', 'Child Page 2', '2022-02-23 23:25:41', null),
('fefeee9f9e9fee', '2', null, null, 'root', 'Root Page With No Children', null, null),
('7eajiefaeiaeaeleailje', '2', null, null, 'root', 'Root Page A', null, null),
('8efjaliejfaelfjael', '2', '7eajiefaeiaeaeleailje', null, 'container', 'Child Page Again 1', '2022-02-04 23:26:01', null), 
('jklfjealkjlfajfl', '2', '7eajiefaeiaeaeleailje', null, 'container', 'Child Page Again 2', '2022-02-07 23:25:41', null);
COMMIT;

SELECT
	test_pages.*
FROM
	test_pages
WHERE
	test_pages.default_page_id IS NULL
AND test_pages.type = "root";

SELECT
  parent_id
  , MIN(created) AS created,
  id
from test_pages 
WHERE test_pages.type = "container"
GROUP BY parent_id, id;

SELECT
	*
FROM
	(
		SELECT
			test_pages.id
		FROM
			test_pages
		WHERE
			test_pages.default_page_id is null
		and test_pages.type = "root"
	) as a
LEFT JOIN(
	SELECT
		parent_id ,
		MIN(id) AS id
	from
		test_pages
	WHERE
		test_pages.type = "container"
	GROUP BY
		parent_id
) as b ON b.parent_id = a.id

id	version_id	parent_id	default_page_id	type	title	created	modified
7eajiefaeiaeaeleailje	2	null	null	root	Root Page A	null	null
abc1212312efff	2	null	null	root	Root Page	null	null
fefeee9f9e9fee	2	null	null	root	Root Page With No Children	null	null

parent_id	created	id
7eajiefaeiaeaeleailje	2022-02-04 23:26:01	8efjaliejfaelfjael
7eajiefaeiaeaeleailje	2022-02-07 23:25:41	jklfjealkjlfajfl
abc1212312efff	2022-02-03 23:26:01	ejejejejejej
abc1212312efff	2022-02-23 23:25:41	jf2039jf932032f

id	parent_id	id
7eajiefaeiaeaeleailje	7eajiefaeiaeaeleailje	8efjaliejfaelfjael
abc1212312efff	abc1212312efff	ejejejejejej
fefeee9f9e9fee	null	null