MySQL 8.0

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

create table Skills
(
  ID varchar(10),
  start_date date,
  end_date date,
  Skill varchar(10)
);

Insert into Skills values
('1','2021-01-01','2021-12-31','A'),
('1','2022-01-01','2022-12-31','B'),
('2','2021-01-01','2021-12-31','A'),
('2','2021-11-30','2022-12-31','B'),
('3','2021-01-01','2021-12-31','A'),
('3','2021-11-30','2022-12-31','B'),
('3','2022-11-30','2023-12-31','C'),
('4','2021-01-01','2021-12-31','A'),
('4','2022-01-01','2022-12-31','B'),
('4','2022-11-30','2023-12-31','C');

Records: 10  Duplicates: 0  Warnings: 0


select d.*,
lag(end_date) over (partition by id order by start_date) as prev_end_date
from Skills d


ID
start_date
end_date
Skill
prev_end_date


1
2021-01-01
2021-12-31
A
null

1
2022-01-01
2022-12-31
B
2021-12-31

2
2021-01-01
2021-12-31
A
null

2
2021-11-30
2022-12-31
B
2021-12-31

3
2021-01-01
2021-12-31
A
null

3
2021-11-30
2022-12-31
B
2021-12-31

3
2022-11-30
2023-12-31
C
2022-12-31

4
2021-01-01
2021-12-31
A
null

4
2022-01-01
2022-12-31
B
2021-12-31

4
2022-11-30
2023-12-31
C
2022-12-31


select d.*, DATEDIFF(prev_end_date, start_date),
      sum(case when DATEDIFF(prev_end_date, start_date) > 0 then 0 else 1 end) over (partition by id order by start_date) as grp
from (
    select d.*,
    lag(end_date) over (partition by id order by start_date) as prev_end_date
    from Skills d
) d


ID
start_date
end_date
Skill
prev_end_date
DATEDIFF(prev_end_date, start_date)
grp


1
2021-01-01
2021-12-31
A
null
null
1

1
2022-01-01
2022-12-31
B
2021-12-31
-1
2

2
2021-01-01
2021-12-31
A
null
null
1

2
2021-11-30
2022-12-31
B
2021-12-31
31
1

3
2021-01-01
2021-12-31
A
null
null
1

3
2021-11-30
2022-12-31
B
2021-12-31
31
1

3
2022-11-30
2023-12-31
C
2022-12-31
31
1

4
2021-01-01
2021-12-31
A
null
null
1

4
2022-01-01
2022-12-31
B
2021-12-31
-1
2

4
2022-11-30
2023-12-31
C
2022-12-31
31
2


select id, min(start_date) as start_date, max(end_date) as end_date, max(Skill) as skill
from (
      select d.*,
      sum(case when DATEDIFF(prev_end_date, start_date) > 0 then 0 else 1 end)  over (partition by id order by start_date) as grp
      from (
            select d.*,
            lag(end_date) over (partition by id order by start_date) as prev_end_date
            from Skills d
      ) d
    ) d
group by id, grp
order by id, min(start_date);


id
start_date
end_date
skill


1
2021-01-01
2021-12-31
A

1
2022-01-01
2022-12-31
B

2
2021-01-01
2022-12-31
B

3
2021-01-01
2023-12-31
C

4
2021-01-01
2021-12-31
A

4
2022-01-01
2023-12-31
C

ID	start_date	end_date	Skill	prev_end_date
1	2021-01-01	2021-12-31	A	null
1	2022-01-01	2022-12-31	B	2021-12-31
2	2021-01-01	2021-12-31	A	null
2	2021-11-30	2022-12-31	B	2021-12-31
3	2021-01-01	2021-12-31	A	null
3	2021-11-30	2022-12-31	B	2021-12-31
3	2022-11-30	2023-12-31	C	2022-12-31
4	2021-01-01	2021-12-31	A	null
4	2022-01-01	2022-12-31	B	2021-12-31
4	2022-11-30	2023-12-31	C	2022-12-31