SQL Server 2019 (Linux)

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

13 rows affected

with
order_data as (
  SELECT id, code, year,
         lag(code) over (partition by id, code order by id, code desc, year) as prev_code,
         lag(year) over (partition by id, code order by id, code desc, year) as prev_year,
         lead(year) over (partition by id, code order by id, code desc, year) as next_year

FROM my_data
),
total_count as (
  SELECT id, count(distinct code) as nb_code
    FROM order_data
   WHERE prev_year IS NOT NULL OR next_year IS NOT NULL
   GROUP BY id
),
detect_repeat as (
  SELECT id, code, year, prev_code, prev_year,
         CASE
           WHEN code = prev_code THEN year - prev_year
           ELSE -1
         END as repeat_code
    FROM order_data
),
repeat_count as (
  SELECT id, year, prev_year,
         CONCAT (prev_year, '-', year) as period,
         count(distinct code) as nb_repeat
    FROM detect_repeat
   WHERE repeat_code = 1
   GROUP BY id, year, prev_year
)
  SELECT t.id, period, nb_repeat, nb_code,
         CONVERT(decimal(5,2), ((coalesce(nb_repeat, 0) * 1.0) / nb_code) * 100) as repeat_ratio
    FROM total_count as t
    LEFT JOIN repeat_count as r on t.id = r.id

id	period	nb_repeat	nb_code	repeat_ratio
1	2020-2021	2	2	100.00
1	2021-2022	1	2	50.00
2	2020-2021	1	1	100.00
2	2021-2022	1	1	100.00
3	null	null	1	0.00