sql - Found number of rows before a value changes with a group by

Question

I have a table like this one

CREATE TABLE Levels
    ([userid] int, [counter1] int, [counter2] int, [date] datetime)
;

The counter2 is an incremental value. The date is just the datetime the row was created. The counter1 is a field that can take different integer values. And the userid the id of the user.

This is an example of the data. You can find a bigger example with two users in sqlfiddle

| userid | counter1 | counter2 |                 date |
|--------|----------|----------|----------------------|
|    123 |        6 |       42 | 2010-07-31T00:12:28Z |
|    123 |        6 |       43 | 2010-11-20T00:11:15Z |
|    123 |        6 |       44 | 2011-03-12T00:15:07Z |
|    123 |        5 |       45 | 2011-07-02T01:11:09Z |
|    123 |        5 |       46 | 2011-10-22T00:24:18Z |
|    123 |        5 |       47 | 2012-02-10T23:51:54Z |
|    123 |        5 |       48 | 2012-06-01T23:43:26Z |
|    123 |        5 |       49 | 2012-09-21T23:43:59Z |
|    123 |        4 |       50 | 2013-01-11T23:52:43Z |
|    123 |        4 |       51 | 2013-05-03T23:49:25Z |
|    123 |        4 |       52 | 2013-08-23T23:48:24Z |
|    123 |        3 |       53 | 2013-12-14T00:01:20Z |
|    123 |        3 |       54 | 2014-04-04T23:45:45Z |
|    123 |        4 |       55 | 2014-07-25T23:44:34Z |
|    123 |        5 |       56 | 2014-11-14T23:46:11Z |

What I try to do is to count how many times the counter1 has the same value before it changes. Why the rest of the questions I found in stackoverflow didn't work?

• The counter1 field can get the same value multiple times later on, which I don't want to count as the same case.
• I am working in SQL Server 2008 and LAG function is not available

The desired result for the full example in sqlfiddle is

| userid | counter1 | count |
|--------|----------|-------|
|     123|         6|      3|
|     123|         5|      5|
|     123|         4|      3|
|     123|         3|      2|
|     123|         4|      1|
|     123|         5|      1|
|     123|         6|      2|
|     123|         5|      5|
|     123|         4|      2|
|     123|         5|      1|
|     123|         4|      5|
|     123|         5|      5|
|     345|         6|      2|
|     345|         6|      9|

question from:https://stackoverflow.com/questions/66065930/found-number-of-rows-before-a-value-changes-with-a-group-by

Answer 1

This is a type of gaps-and-islands problem. Fortunately, you can use the difference of row numbers:

select userid, counter1, count(*)
from (select t.*,
             row_number() over (partition by userid order by counter2) as seqnum,
             row_number() over (partition by userid, counter1 order by counter2) as seqnum_2
      from t
     ) t
group by userid, counter1, (seqnum - seqnum_2)
order by userid, min(counter2);

Note: This assumes that the ordering is based on counter2. If it is really based on date then you can use that column instead.

Why this works is a little tricky to explain. But if you look at the results from the subquery, you will see how the difference between the two row_number() values is constant when counter1 has the same value on adjacent rows.