MySQL 8.0

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

CREATE TABLE objects (
    id int AUTO_INCREMENT PRIMARY KEY,
    parent_id INT(11),
    foreign_key INT(11),
    start_date INT(11),
    end_date INT(11)
)
INSERT INTO objects VALUES 
(1, 0, 1, 1638930577, 1638930578),
(2, 1, 1, 1638930578, 1638930578),
(3, 2, 1, 1638930578, 1638930578),
(4, 0, 2, 1638930576, 1638930578),
(5, 4, 2, 1638930578, 1638930578),
(6, 5, 2, 1638930578, 1638930578),
(7, 0, 1, 1638930574, 1638930578),
(8, 7, 1, 1638930578, 1638930578),
(9, 7, 1, 1638930578, 1638930579)
with recursive rcte as (
    select id as group_id, foreign_key, start_date, end_date, id
    from objects
    where parent_id = 0
    union all
    select p.group_id, p.foreign_key, c.start_date, c.end_date, c.id
    from rcte as p
    join objects as c on c.parent_id = p.id
)
select group_id, foreign_key, min(start_date) as start_date, max(end_date) as end_date, count(*) as number_of_objects
from rcte
group by group_id, foreign_key


group_id
foreign_key
start_date
end_date
number_of_objects


1
1
1638930577
1638930578
3

4
2
1638930576
1638930578
3

7
1
1638930574
1638930579
3

group_id	foreign_key	start_date	end_date	number_of_objects
1	1	1638930577	1638930578	3
4	2	1638930576	1638930578	3
7	1	1638930574	1638930579	3