MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.3.11-MariaDB-1:10.3.11+maria~stretch-log


CREATE TABLE tbl (
  `group` VARCHAR(1),
  `date` VARCHAR(8),
  `color` VARCHAR(1)
);
INSERT INTO tbl
  (`group`, `date`, `color`)
VALUES
  ('A', '1-1-2019', 'R'),
  ('A', '1-2-2019', 'Y'),
  ('B', '1-1-2019', 'R'),
  ('B', '1-2-2019', 'Y'),
  ('B', '1-3-2019', 'Y'),
  ('B', '1-4-2019', 'R'),
  ('B', '1-5-2019', 'R'),
  ('B', '1-6-2019', 'R');
set @seq := 0, @rank := 0, @prev := 'B'
SELECT 
    *, 
    IF(color='R', @seq := IF(@prev = color, @seq + 1, 1), NULL) AS rank,
    @prev := color as prev
FROM tbl
ORDER BY `group`, `date`


group
date
color
rank
prev


A
1-1-2019
R
1
R

A
1-2-2019
Y
null
Y

B
1-1-2019
R
1
R

B
1-2-2019
Y
null
Y

B
1-3-2019
Y
null
Y

B
1-4-2019
R
1
R

B
1-5-2019
R
2
R

B
1-6-2019
R
3
R

group	date	color	rank	prev
A	1-1-2019	R	1	R
A	1-2-2019	Y	null	Y
B	1-1-2019	R	1	R
B	1-2-2019	Y	null	Y
B	1-3-2019	Y	null	Y
B	1-4-2019	R	1	R
B	1-5-2019	R	2	R
B	1-6-2019	R	3	R