Oracle 18c

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

with road_inspections

(road_id, year, cond) as (

select 1, 2009, 17 from dual union all

select 1, 2011, 16 from dual union all

select 1, 2015, 14 from dual union all

select 1, 2016, 18.3 from dual union all

select 1, 2019, 18.1 from dual union all

select 2, 2013, 17.5 from dual union all

select 2, 2016, 18 from dual union all

select 2, 2019, 18 from dual union all

select 2, 2022, 18 from dual union all

select 3, 2022, 20 from dual)

select * from road_inspections