How do I add an autoincrement Counter based on Conditions and conditional reset in Google-Bigquery

Question

Have my table in Big query and have a problem getting an incremental field based on a condition.

Basically every time the score hits below 95% it returns Stage 1 for the first week. If it hits below 95% for a second straight week it returns Stage 2 etc etc. however, if it goes above 95 % the counter resets to "Good". and thereafter returns Stage 1 if it goes below 95% etc etc.

Answer 1

You can use row_number() -- but after assigning a group based on the count of > 95% values up to each row:

select t.*,
       (case when row_number() over (partition by grp order by month, week) = 1
             then 'Good'
             else concat('Stage ', row_number() over (partition by grp order by month, week) - 1)
        end) as level
from (select t.*,
             countif(score > 0.95) over (order by month, week) as grp
      from t
     ) t;