I would like to know if there is a way to count the number of times a row repeats a value back to back until it becomes false, regardless if it becomes true later on.
Example below is that colours bought and when they are used. When a colour is bought, i would like to track how times it is used until it is swapped off
The rule is: Once an item has been purchased, how many times do they use the newly purchased item until they swap off. For Red's example, he uses it 3 times until he changes and i want to tally just that, even if he returns to it Green was not used immediately and then has zero uses once purchased.
Example Data
| Order_time | Colour Bought Most Recently | Colour Used | Desired Result |
|---|---|---|---|
| 1 | Red | Red | 1 |
| 2 | Red | Red | 2 |
| 3 | Red | Red | 3 |
| 4 | Red | Blue | 3 |
| 5 | Red | Red | 3 |
| 1 | Green | Blue | 0 |
| 2 | Green | Red | 0 |
| 3 | Green | Green | 0 |
Thanks in advance
I have attempted to utilise Last_value and lag. I have also attempted to utilise a join to the table and the min() the first time they column 1 to column 2 do not equal the main issue is that i am unable or do not know how to select the previous value of the same column
I am thinking mostly similar to excel where the formula is in Column C and i check if Column C previous Row was correct or not.
You'll need to be able to order the rows via some kind of sequence or time data:
with flagged as (
select *,
case when bought <> lag(bought, 1, '')
over (order by order_time) then 1 else 0 end as flag
from T
), grouped as (
select *,
sum(flag) over (order by order_time) as grp
from flagged
), tallied as (
select *,
min(case when bought <> used then 0 else 1 end)
over (partition by grp order by order_time) as tally
from grouped
)
select *,
sum(tally) over (partition by grp order by order_time)
from tallied;
Maybe there's a slightly cleaner way but this is a fairly standard gaps and islands technique.
See it in action here: https://dbfiddle.uk/TFFHRLz3