MariaDB 10.4

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

create table t as
    select 1 as athleteId, '2020-01-04' as date,  '5K ' as distance, time('00:30:00') as finishTime union all
    select 1 as athleteId, '2020-01-11' as date,  '5K ' as distance, '00:30:09' as time union all
    select 1 as athleteId, '2020-01-18' as date,  '5K ' as distance, '00:29:45' as time union all
    select 1 as athleteId, '2020-01-25' as date,  '5K ' as distance, '00:29:32' as time union all
    select 1 as athleteId, '2020-02-01' as date,  '5K ' as distance, '00:31:18' as time union all
    select 1 as athleteId, '2020-02-02' as date,  '10K' as distance, '1:06:07' as time union all
    select 1 as athleteId, '2020-02-08' as date,  '5K ' as distance, '00:28:25' as time union all
    select 1 as athleteId, '2020-02-23' as date,  '10K' as distance, '1:06:02' as time union all
    select 1 as athleteId, '2020-02-23' as date,  '10K' as distance, '1:07:30' as time 
    
select t.*,
       (case when finishTime >=
                  min(finishTime) over (partition by athleteid, distance
                                        order by date
                                        rows between unbounded preceding and 1 preceding
                                       )
             then 'N' else 'Y'
        end) as isPersonalBest
from t
order by athleteId, date;


athleteId
date
distance
finishTime
isPersonalBest


1
2020-01-04
5K
00:30:00
Y

1
2020-01-11
5K
00:30:09
N

1
2020-01-18
5K
00:29:45
Y

1
2020-01-25
5K
00:29:32
Y

1
2020-02-01
5K
00:31:18
N

1
2020-02-02
10K
1:06:07
Y

1
2020-02-08
5K
00:28:25
Y

1
2020-02-23
10K
1:06:02
Y

1
2020-02-23
10K
1:07:30
N


select t.*,
       (case when not exists (select 1
                              from t t2
                              where t2.athleteId = t.athleteId and
                                    t2.distance = t.distance and
                                    t2.date < t.date and
                                    t2.finishTime <= t.finishTime
                                  )
             then 'Y' else 'N'
        end) as isPersonalBest
from t
order by athleteId, date;


athleteId
date
distance
finishTime
isPersonalBest


1
2020-01-04
5K
00:30:00
Y

1
2020-01-11
5K
00:30:09
N

1
2020-01-18
5K
00:29:45
Y

1
2020-01-25
5K
00:29:32
Y

1
2020-02-01
5K
00:31:18
N

1
2020-02-02
10K
1:06:07
Y

1
2020-02-08
5K
00:28:25
Y

1
2020-02-23
10K
1:06:02
Y

1
2020-02-23
10K
1:07:30
N

athleteId	date	distance	finishTime	isPersonalBest
1	2020-01-04	5K	00:30:00	Y
1	2020-01-11	5K	00:30:09	N
1	2020-01-18	5K	00:29:45	Y
1	2020-01-25	5K	00:29:32	Y
1	2020-02-01	5K	00:31:18	N
1	2020-02-02	10K	1:06:07	Y
1	2020-02-08	5K	00:28:25	Y
1	2020-02-23	10K	1:06:02	Y
1	2020-02-23	10K	1:07:30	N