sqlranking-functions

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


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?


Solution

  • 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