PARTITION rows by status

I have a query which fetches result by status partition as rank. but I need a result as given below.

COUNTERID   status  transaction_time    RANK
121 NEW Nov-09-2019 8:32:19 1
121 NEW Nov-09-2019 8:32:19 2
121 CLAIMED Nov-09-2019 8:32:21 1
121 CLAIMED Nov-09-2019 8:32:21 2
121 NEW Nov-09-2019 8:32:59 1
121 CLAIMED Nov-09-2019 8:32:59 2
121 RESOLVED    Nov-09-2019 8:33:30 1
121 RESOLVED    Nov-09-2019 8:49:58 2
121 RESOLVED    Nov-13-2019 6:51:11 3
222 NEW Nov-11-2019 22:15:52    1
222 NEW Nov-11-2019 22:15:54    2
222 RESOLVED    Nov-11-2019 22:15:54    1
222 NEW Nov-11-2019 22:15:55    1
222 CLAIMED Nov-11-2019 22:16:24    1
222 CLAIMED Nov-11-2019 22:16:24    2
222 RESOLVED    Nov-11-2019 22:16:56    1
222 CLAIMED Nov-11-2019 22:33:06    1
222 RESOLVED    Nov-12-2019 7:39:00 1
222 RESOLVED    Nov-12-2019 23:59:45    2



SELECT COUNTERID, status, transaction_time, ROW_number()
                OVER (
                  partition by COUNTERID, status
                  order by COUNTERID, transaction_time
                ) AS RANK
FROM COUNTER_HISTORY

Please help in getting the desired result. Thanks in advance. Using #standardsql

Answer

This is a gaps-and-islands problem. The difference of row numbers should do what you want to identify the groups:

select ch.* except (seqnum, seqnum_s),
       row_number() over (partition by counter_id, status, (seqnum_s - seqnum)
                          order by transaction_time
                         ) as ranking
from (select ch.*,
             row_number() over (partition by counter_id order by transaction_time) as seqnum,
             row_number() over (partition by counter_id, status order by transaction_time) as seqnum_s
      from counter_history ch
     ) ch;

It is a little big cumbersome to explain why the difference of row numbers identifies adjacent rows with the same value. If you look at the results of the subquery, though, you should see how this works.