Can SQL dense rank work for count of (non distinct) value changes with respect to time

Question

Using SQL I have a list of credits for a course by year. I want to get a count of how many values the 'credits' attribute has been over years. If a value occurs twice (non consecutively) I want it counted. As follows, you can see there are 3 distinct values for CREDITS (5, 7.5, 6.5) over the timeframe but if credits have been values (5, 7.5, 6.5 and then back to 5) over the years, I want 4 as the result.

Dataset and desired result

Dense rank is not working and does not give me what I want as just gives me distinct values in the timeframe, rather than 4 (non distinct) values across the timeframe given, with respect to time. Is this doable in SQL?

Answer 1

You can use a running sum with a case expression that checks if the credits value has been changed:

select year_, credits,
  sum(case when credits<>pre_credits then 1 else 0 end)
  over (order by year_ desc) + 1 res_rnk
from
(
  select *,
    lag(credits, 1, credits) over (order by year_ desc) pre_credits
  from tbl
) t
order by year_ desc

demo