By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table T (dt int, bought varchar(10), used varchar(10));
insert into T (dt, bought, used) values
(1, 'Red', 'Red'),
(2, 'Red', 'Red'),
(3, 'Red', 'Red'),
(4, 'Red', 'Blue'),
(5, 'Red', 'Red'),
(6, 'Green', 'Blue'),
(7, 'Green', 'Red'),
(8, 'Green', 'Green');
8 rows affected
with flagged as (
select *,
case when bought <> lag(bought, 1, '')
over (order by dt) then 1 else 0 end as flag
from T
), grouped as (
select *,
sum(flag) over (order by dt) as grp
from flagged
), tallied as (
select *,
min(case when bought <> used then 0 else 1 end)
over (partition by grp order by dt) as tally
from grouped
)
select *,
sum(tally) over (partition by grp order by dt)
from tallied;
| dt | bought | used | flag | grp | tally | (No column name) |
|---|---|---|---|---|---|---|
| 1 | Red | Red | 1 | 1 | 1 | 1 |
| 2 | Red | Red | 0 | 1 | 1 | 2 |
| 3 | Red | Red | 0 | 1 | 1 | 3 |
| 4 | Red | Blue | 0 | 1 | 0 | 3 |
| 5 | Red | Red | 0 | 1 | 0 | 3 |
| 6 | Green | Blue | 1 | 2 | 0 | 0 |
| 7 | Green | Red | 0 | 2 | 0 | 0 |
| 8 | Green | Green | 0 | 2 | 0 | 0 |
-- alternate approach
with flagged as (
select *,
case when bought <> lag(bought, 1, '') over (order by dt) then 1 else 0 end as flag1,
case when bought = lag(bought, 1, '') over (order by dt) and
used <> lag(used, 1, '') over (order by dt) then 1 else 0 end as flag2
from T
), grouped1 as (
select *,
sum(flag1) over (order by dt) as grp1
from flagged
), grouped2 as (
select *,
sum(flag2) over (partition by grp1 order by dt) + 1 as grp2
from grouped1
)
select *,
count(case when bought = used and grp2 = 1 then 1 end) over (partition by grp1 order by dt)
from grouped2
| dt | bought | used | flag1 | flag2 | grp1 | grp2 | (No column name) |
|---|---|---|---|---|---|---|---|
| 1 | Red | Red | 1 | 0 | 1 | 1 | 1 |
| 2 | Red | Red | 0 | 0 | 1 | 1 | 2 |
| 3 | Red | Red | 0 | 0 | 1 | 1 | 3 |
| 4 | Red | Blue | 0 | 1 | 1 | 2 | 3 |
| 5 | Red | Red | 0 | 1 | 1 | 3 | 3 |
| 6 | Green | Blue | 1 | 0 | 2 | 1 | 0 |
| 7 | Green | Red | 0 | 1 | 2 | 2 | 0 |
| 8 | Green | Green | 0 | 1 | 2 | 3 | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.