Q:

Add Counter Column for Contiguous Run of Equal Value

I believe I have a pretty straightforward question, but was not able to find the answer I was looking for by searching through previously asked questions on the site.
I am attempting to add a counter column to my data, such as in the example below. I would like it to track consecutive occurances of a single value in a specific column of my data. Even though, ideally, the counter would function by group (ID #), it is not completely necessary, as the chance of a consecutive repeat value between two IDs is essentially 0.   
    # ID Date   Value  Consecutive_Repeat
    # 1  01-01    a       0
    # 1  01-02    b       0
    # 1  01-03    b       1
    # 1  01-04    a       0
    # 2  01-01    x       0
    # 2  01-02    x       1
    # 2  01-03    x       2
    # 2  01-04    a       0   
    # 3  01-01    b       0
    # 3  01-02    b       1
    # 3  01-03    a       0
    # 3  01-04    b       0
    # 4  01-01    c       0
    # 4  01-02    c       1
    # 4  01-03    c       2
    # 4  01-04    c       3

A:

It's a bit more complicated than a simple ROW_NUMBER, you can apply a Teradata extension to Standard SQL, RESET WHEN:
Row_Number() 
Over (PARTITION BY id 
      ORDER BY date
      RESET WHEN Min(value) -- start whenever there's a a new value
                 Over (PARTITION BY id
                       ORDER BY date
                       ROWS BETWEEN 1 Preceding AND 1 Preceding) <> value) -1

