Postgres 11

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

create table Racings (id int, track int, car int, circle_time int);

insert into Racings values (10, 1, 10, 15), (9, 1, 10, 14), (8, 1, 10, 16)
, (7, 1, 10, 15), (6, 1, 10, 13), (5, 2, 10, 7), (4, 2, 10, 4), (3, 2, 10, 5)
, (2, 3, 10, 8), (1, 3, 10, 10);

CREATE TABLE



INSERT 0 10


SELECT *
     , CASE WHEN next_circle_time IS NULL OR next_next_circle_time IS NULL
               THEN NULL
            ELSE ((t.circle_time + COALESCE(next_circle_time, 0) + COALESCE(next_next_circle_time, 0)) / 3 :: DECIMAL) :: DECIMAL(10, 2)
        END avg_circle_time
FROM (SELECT *
           , LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) next_circle_time
           , LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) next_next_circle_time
      FROM Racings) t


id
track
car
circle_time
next_circle_time
next_next_circle_time
avg_circle_time


10
1
10
15
14
16
15.00

9
1
10
14
16
15
15.00

8
1
10
16
15
13
14.67

7
1
10
15
13
null
null

6
1
10
13
null
null
null

5
2
10
7
4
5
5.33

4
2
10
4
5
null
null

3
2
10
5
null
null
null

2
3
10
8
10
null
null

1
3
10
10
null
null
null



SELECT 10


SELECT *
     , CASE WHEN LEAD(circle_time, 2) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
                 OR LEAD(circle_time, 1) OVER (PARTITION BY track ORDER BY id DESC) IS NULL
               THEN NULL
            ELSE AVG(circle_time) OVER (PARTITION BY track ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
       END :: DECIMAL(10, 2) avg_circle_time       
FROM Racings


id
track
car
circle_time
avg_circle_time


10
1
10
15
15.00

9
1
10
14
15.00

8
1
10
16
14.67

7
1
10
15
null

6
1
10
13
null

5
2
10
7
5.33

4
2
10
4
null

3
2
10
5
null

2
3
10
8
null

1
3
10
10
null



SELECT 10

id	track	car	circle_time	next_circle_time	next_next_circle_time	avg_circle_time
10	1	10	15	14	16	15.00
9	1	10	14	16	15	15.00
8	1	10	16	15	13	14.67
7	1	10	15	13	null	null
6	1	10	13	null	null	null
5	2	10	7	4	5	5.33
4	2	10	4	5	null	null
3	2	10	5	null	null	null
2	3	10	8	10	null	null
1	3	10	10	null	null	null