Postgres 12

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

--explain analyze verbose 
WITH managers AS (
	SELECT
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS d_range
	FROM (
		VALUES 
			(1, 'team_lead', '2021-01-01'::date, '2021-12-31'::date),
			(2, 'ceo', '2021-01-01'::date, '9999-12-31'::date)
	) a (position_id, manager, start_date, end_date)
),
func_managers AS (
	SELECT 
		position_id,
		manager,
		start_date,
		end_date,
		daterange(start_date, end_date, '[]') AS d_range
	FROM (
		VALUES
			(1, 'ceo', '2021-01-01'::date, '2021-06-20'::date),
			(1, 'hr', '2021-06-25'::date, '2021-08-20'::date)
	) a (position_id, manager, start_date, end_date)
),
manager_dates AS (
	SELECT 
		a.*, 
		ROW_NUMBER() OVER (PARTITION BY position_id ORDER BY range_date) AS rn
	  FROM (
			SELECT position_id, start_date
			FROM managers

UNION ALL

SELECT position_id, end_date
			FROM managers

UNION ALL

SELECT position_id, start_date
			FROM func_managers

UNION ALL

SELECT position_id, end_date
			FROM func_managers
	  ) a (position_id, range_date)
),
manager_ranges AS (
	SELECT
		startRange.position_id,
		startRange.range_date AS start_date,
		endRange.range_date AS end_date
	FROM manager_dates startRange
	JOIN manager_dates endRange ON 
		endRange.position_id = startRange.position_id
		AND endRange.rn = startRange.rn + 1
		AND endRange.range_date <> startRange.range_date
),
unified_managers AS (
	SELECT 
		manager_ranges.position_id,
		manager_ranges.start_date,
		manager_ranges.end_date,
		COALESCE(func_managers.manager, managers.manager) AS manager,

CASE func_managers.manager IS NOT NULL
			WHEN TRUE THEN 1
			ELSE 0
		END AS "is_override"
	FROM manager_ranges
	LEFT JOIN managers ON
		managers.position_id = manager_ranges.position_id
		AND managers.start_date <= manager_ranges.start_date
		AND managers.end_date >= manager_ranges.end_date
	LEFT JOIN func_managers ON
		func_managers.position_id = manager_ranges.position_id
		AND func_managers.start_date <= manager_ranges.start_date
		AND func_managers.end_date >= manager_ranges.end_date
	WHERE
		managers.manager IS NOT NULL 
		OR func_managers.manager IS NOT NULL
	ORDER BY position_id, start_date
)
SELECT
	position_id,
	manager,

-- make sure the date ranges don't overlap (start_date cannot be equal to prev row end_date)
	CASE is_override = 0 AND LAG(end_date) OVER (PARTITION BY position_id) = start_date
		WHEN TRUE THEN (start_date + INTERVAL '1 day')::date
		ELSE start_date
	END AS start_date,

-- make sure the date ranges don't overlap (end_date cannot be equal to next row start_date)
	CASE is_override = 0 AND LEAD(start_date) OVER (PARTITION BY position_id) = end_date
		WHEN TRUE THEN (end_date - INTERVAL '1 day')::date
		ELSE end_date
	END AS end_date
FROM unified_managers;

position_id	manager	start_date	end_date
1	ceo	2021-01-01	2021-06-20
1	team_lead	2021-06-21	2021-06-24
1	hr	2021-06-25	2021-08-20
1	team_lead	2021-08-21	2021-12-31
2	ceo	2021-01-01	9999-12-31

SELECT 5