Resetting Running Total based on value in another column per customerID - SQL Server

Question

I've already tried looking on here for the answer to this question, and although have found similar queries, I haven't found this exact one.

I'm looking to count the instances of a customer getting a certain score, and if they get less than that score I'd like the count to be reset.

This is the data I have:

And this is the result I would like to produce:

Any help would be appreciated, as well as an explanation of any advanced code used.

Answer 1

You can define a group by the number of times the value is less than your threshold. That defines each group. After that, you want a row number:

select t.*,
       (case when t.score < 1 then 0
             else row_number() over (partition by t.customerId, grp, score order by t.attempt)
        end) as ranking
from (select t.*,
             sum(case when score < 1 then 1 else 0 end) over (partition by t.customerId order by t.attempt) as grp
      from t
     ) t;

Here is a db<>fiddle.