Oracle 18c

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

CREATE TABLE result ( competition_id, sportsman_id, hold_date ) AS
SELECT 1, 1, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-02-01' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-03-01' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2020-04-01' FROM DUAL UNION ALL
SELECT 5, 1, DATE '2020-05-01' FROM DUAL UNION ALL
SELECT 1, 2, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2020-02-01' FROM DUAL UNION ALL
SELECT 4, 2, DATE '2020-04-01' FROM DUAL UNION ALL
SELECT 5, 2, DATE '2020-05-01' FROM DUAL UNION ALL
SELECT 2, 3, DATE '2020-02-01' FROM DUAL UNION ALL
SELECT 4, 3, DATE '2020-04-01' FROM DUAL UNION ALL
SELECT 1, 4, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 3, 4, DATE '2020-03-01' FROM DUAL UNION ALL
SELECT 5, 4, DATE '2020-05-01' FROM DUAL UNION ALL
SELECT 1, 5, DATE '2020-01-01' FROM DUAL UNION ALL
SELECT 2, 5, DATE '2020-02-01' FROM DUAL UNION ALL
SELECT 5, 5, DATE '2020-05-01' FROM DUAL;

17 rows affected

  select  competition_id, sportsman_id, hold_date , add_months(hold_date,-1*row_number() over(partition by sportsman_id order by hold_date)) island
  from    result
  order by sportsman_id, hold_date 


COMPETITION_ID
SPORTSMAN_ID
HOLD_DATE
ISLAND


1
1
01-JAN-20
01-DEC-19

2
1
01-FEB-20
01-DEC-19

3
1
01-MAR-20
01-DEC-19

4
1
01-APR-20
01-DEC-19

5
1
01-MAY-20
01-DEC-19

1
2
01-JAN-20
01-DEC-19

2
2
01-FEB-20
01-DEC-19

4
2
01-APR-20
01-JAN-20

5
2
01-MAY-20
01-JAN-20

2
3
01-FEB-20
01-JAN-20

4
3
01-APR-20
01-FEB-20

1
4
01-JAN-20
01-DEC-19

3
4
01-MAR-20
01-JAN-20

5
4
01-MAY-20
01-FEB-20

1
5
01-JAN-20
01-DEC-19

2
5
01-FEB-20
01-DEC-19

5
5
01-MAY-20
01-FEB-20


select sportsman_id, min(hold_date) , max(hold_date), comps_in_island
from (
 select  competition_id, sportsman_id, hold_date, island, count(*) over (partition by sportsman_id,island) comps_in_island
 from (
  select  competition_id, sportsman_id, hold_date , add_months(hold_date,-1*row_number() over(partition by sportsman_id order by hold_date)) island
  from    result
 )
)
where comps_in_island > 1
group by sportsman_id, island, comps_in_island;


SPORTSMAN_ID
MIN(HOLD_DATE)
MAX(HOLD_DATE)
COMPS_IN_ISLAND


5
01-JAN-20
01-FEB-20
2

2
01-JAN-20
01-FEB-20
2

2
01-APR-20
01-MAY-20
2

1
01-JAN-20
01-MAY-20
5

COMPETITION_ID	SPORTSMAN_ID	HOLD_DATE	ISLAND
1	1	01-JAN-20	01-DEC-19
2	1	01-FEB-20	01-DEC-19
3	1	01-MAR-20	01-DEC-19
4	1	01-APR-20	01-DEC-19
5	1	01-MAY-20	01-DEC-19
1	2	01-JAN-20	01-DEC-19
2	2	01-FEB-20	01-DEC-19
4	2	01-APR-20	01-JAN-20
5	2	01-MAY-20	01-JAN-20
2	3	01-FEB-20	01-JAN-20
4	3	01-APR-20	01-FEB-20
1	4	01-JAN-20	01-DEC-19
3	4	01-MAR-20	01-JAN-20
5	4	01-MAY-20	01-FEB-20
1	5	01-JAN-20	01-DEC-19
2	5	01-FEB-20	01-DEC-19
5	5	01-MAY-20	01-FEB-20