-1

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:

Data Before Running Total

And this is the result I would like to produce:

With Running Total

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Amar
  • 37
  • 7
  • 3
    Welcome to Stack Overflow. Images of data are poorly received as the volunteers you're asking to help you can't make use of it. Please post your sample data, and expected results, as **formatted** `text` or as DDL (`CREATE`) and DML (`INSERT`) statements. Thanks. – Thom A Dec 19 '18 at 18:49
  • [This answer](https://stackoverflow.com/questions/46829622/break-row-number-sequence-based-on-flag-variable) should be helpful to you: Your flag variable will simply be whether to include the attempt (i.e., it's >= 1.0). – Zack Dec 19 '18 at 19:34

2 Answers2

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. I think this code gets me most of the way there, however when there is a 1 after a value that's < 1, the group number stays the same, and this throws out the ranking: Here's the code I'm using: – Amar Dec 20 '18 at 10:43
  • @Amar . . . I made a small change and added a db<>fiddle. – Gordon Linoff Dec 20 '18 at 11:46
0
DECLARE @T table (CustomerID int, Attempt int, score decimal(10,2) )
INSERT INTO @T
VALUES
(111, 1, 1)
,(111, 2, 1)
,(111, 3, 1)
,(111, 4, 0.5)
,(111, 5, 1)
,(111, 6, 0)
,(222, 5, 0.5)
,(222, 6, 1)
,(222, 7, 0.5)
,(222, 8, 1)
,(222, 9, 1)
,(222, 110, 1)
select t.*,
   (case when t.score < 1 then 0
         else row_number() over (partition by t.customerId, grp order by t.attempt)
    end) as ranking
from (select t.*,
         sum(case when score < 1.00 then 1 else 0 end) over (partition by t.customerId order by t.attempt) as grp
  from @t t
 ) t;
Amar
  • 37
  • 7