SQL group by changing column

Question

Suppose I have a table sorted by date as so:

+-------------+--------+
|    DATE     |  VALUE |
+-------------+--------+
|  01-09-2020 |   5    |
|  01-15-2020 |   5    |
|  01-17-2020 |   5    |
|  02-03-2020 |   8    |
|  02-13-2020 |   8    |
|  02-20-2020 |   8    |
|  02-23-2020 |   5    |
|  02-25-2020 |   5    |
|  02-28-2020 |   3    |
|  03-13-2020 |   3    |
|  03-18-2020 |   3    |
+-------------+--------+

I want to group by changes in value within that given date range, and add a value that increments each time as an added column to denote that.

I have tried a number of different things, such as using the lag function:

SELECT value, value - lag(value) over (order by date) as count
GROUP BY value

In short, I want to take the table above and have it look like:

+-------------+--------+-------+
|    DATE     |  VALUE | COUNT |
+-------------+--------+-------+
|  01-09-2020 |   5    |   1   |
|  01-15-2020 |   5    |   1   |
|  01-17-2020 |   5    |   1   |
|  02-03-2020 |   8    |   2   |
|  02-13-2020 |   8    |   2   |
|  02-20-2020 |   8    |   2   |
|  02-23-2020 |   5    |   3   |
|  02-25-2020 |   5    |   3   |
|  02-28-2020 |   3    |   4   |
|  03-13-2020 |   3    |   4   |
|  03-18-2020 |   3    |   4   |
+-------------+--------+-------+

I want to eventually have it all in one small table with the earliest date for each.

+-------------+--------+-------+
|    DATE     |  VALUE | COUNT |
+-------------+--------+-------+
|  01-09-2020 |   5    |   1   |
|  02-03-2020 |   8    |   2   |
|  02-23-2020 |   5    |   3   |
|  02-28-2020 |   3    |   4   |
+-------------+--------+-------+

Any help would be very appreciated

Answer 1

you can use a combination of Row_number and Dense_rank functions to get the required results like below:

;with cte
as
(
select t.DATE,t.VALUE
,Dense_rank() over(partition by t.VALUE order by t.DATE) as d_rank
,Row_number() over(partition by t.VALUE order by t.DATE) as r_num
from table t 
)
Select t.Date,t.Value,d_rank as count
from cte
where r_num = 1

Answer 2

You can use a lag and cumulative sum and a subquery:

SELECT value,
       SUM(CASE WHEN prev_value = value THEN 0 ELSE 1 END) OVER (ORDER BY date)
FROM (SELECT t.*, LAG(value) OVER (ORDER BY date) as prev_value
      FROM t
     ) t

Here is a db<>fiddle.

Answer 3

You can recursively use lag() and then row_number() analytic functions :

WITH t2 AS
(
SELECT LAG(value,1,value-1) OVER (ORDER BY date) as lg,
       t.*
  FROM t
)
SELECT t2.date,t2.value, ROW_NUMBER() OVER (ORDER BY t2.date) as count
  FROM t2
 WHERE value - lg != 0 

Demo

and filter through inequalities among the returned values from those functions.