It's possible but it's a bit tricky. One of the key point is to sort by id and code, not by year: using window functions, it allows to identify the changes for each.
I propose you a solution built using MySQL, using Common Table Expressions; one and the following logic:
- Identify code and year changes from one line to another
- Use this intermediate result to calculate the basis counts (and reject values that comes only ones - to be adapted if necessary)
- Mark changes - identify rows where a repetition occur
- Count values to be considered (ie for each id, number of codes that appear continuously)
- calculate summary / percentages (to be adapted to your dbms and results formatted accordingly)
Here is the code for my proposition:
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
Please feel free to play this code and adapt it thanks to DB Fiddle
EDIT: I updated the query to adapt it to SQL Server and to add ids where there are no changes.
If you really need to build some columns for the periods, instead of dedicated row, you'll need to set it up in a transactional program according to your dbms
