Get consecutive count on repeated values with complex partition by in Postgres

Given a set of 3 columns (blue columns: student, semester, grade) I need to calculate for how many consecutive semesters has each student had the same grade (green column) using a query in Postgres.

(first consecutive semester with avg 8: consec = 1. second consec semester with avg 8: consec = 2…)

Sample data fiddle: https://www.db-fiddle.com/f/v31a5Bpere26tXQb5L1fFJ/0

Expected result:

I have tried using

ROW_NUMBER() OVER(partition by student)

ROW_NUMBER() OVER(partition by avg_grade)

ROW_NUMBER() OVER(partition by student, avg_grade)

ROW_NUMBER() OVER(partition by student, semester)

But none of the above get the expected results.

Thanks a lot for your help!

Answer

This is a type of gap and islands problem. The simplest method is probably to define the groups using the difference between a sequence per grade and semester. Then use row_number() on more time:

select g.*,
       row_number() over (partition by student, avg_grade, semester - seqnum order by semester) as consec_avg
from (select g.*,
             row_number() over (partition by student, avg_grade order by semester) as seqnum
      from grades
     ) g;

Here is a db<>fiddle.