By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
(Person varchar(2), month int, color varchar(4))
;
INSERT INTO t
(Person, month, color)
VALUES
('A', 12, 'Blue'),
('A', 11, 'Blue'),
('A', 10, 'Red'),
('A', 9, 'Red'),
('B', 12, 'Red'),
('B', 11, 'Blue'),
('B', 1, 'Red')
;
7 rows affected
select *
,case when lag(color) over(partition by person order by month) <> color and color = 'Blue' then 1 else 0 end as change
from t
| Person | month | color | change |
|---|---|---|---|
| A | 9 | Red | 0 |
| A | 10 | Red | 0 |
| A | 11 | Blue | 1 |
| A | 12 | Blue | 0 |
| B | 1 | Red | 0 |
| B | 11 | Blue | 1 |
| B | 12 | Red | 0 |