MySQL 8.0

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

CREATE TABLE `survey_daily` (
  `id` int(11) NOT NULL,
  `user_id` varchar(30) NOT NULL,
  `color` varchar(10) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `survey_daily` (`id`, `user_id`, `color`, `timestamp`) VALUES
(1, '3236', "ORANGE", '2020-05-12 02:40:59'),
(2, '3236', "WHITE", '2020-05-13 02:40:59'),
(3, '3236', "ORANGE", '2020-05-14 02:40:59'),
(4, '3236', "ORANGE", '2020-05-15 02:40:59'),
(5, '3237', "ORANGE", '2020-05-15 02:40:59'),
(6, '3237', "ORANGE", '2020-05-16 02:40:59'),
(7, '3236', "ORANGE", '2020-05-16 02:40:59')
select 
    user_id, 
    count(*) no_records, 
    min(timestamp) start_timestamp, 
    max(timestamp) max_timestamp
from (
    select 
        s.*,
        row_number() over(partition by user_id order by timestamp) rn1,
        row_number() over(partition by user_id, color order by timestamp) rn2
    from survey_daily s
) t
where color = 'ORANGE'
group by user_id, rn1 - rn2
order by user_id, start_timestamp


user_id
no_records
start_timestamp
max_timestamp


3236
1
2020-05-12 02:40:59
2020-05-12 02:40:59

3236
3
2020-05-14 02:40:59
2020-05-16 02:40:59

3237
2
2020-05-15 02:40:59
2020-05-16 02:40:59


select *
from (
	select 
		user_id, 
		count(*) no_records, 
		min(timestamp) start_timestamp, 
		max(timestamp) max_timestamp,
		row_number() over(partition by user_id order by count(*) desc) rn
	from (
		select 
			s.*,
			row_number() over(partition by user_id order by timestamp) rn1,
			row_number() over(partition by user_id, color order by timestamp) rn2
		from survey_daily s
	) t
	where color = 'ORANGE'
	group by user_id, rn1 - rn2
) t
where rn = 1
order by user_id, start_timestamp


user_id
no_records
start_timestamp
max_timestamp
rn


3236
3
2020-05-14 02:40:59
2020-05-16 02:40:59
1

3237
2
2020-05-15 02:40:59
2020-05-16 02:40:59
1

user_id	no_records	start_timestamp	max_timestamp
3236	1	2020-05-12 02:40:59	2020-05-12 02:40:59
3236	3	2020-05-14 02:40:59	2020-05-16 02:40:59
3237	2	2020-05-15 02:40:59	2020-05-16 02:40:59