How to count how many consecutive days a user is tagged as orange in MYSQL?

This is a gaps-and-island problems. If you are running MySQL 8.0, an approach is to use the difference between row_numbers()s to build groups of consecutive records where a user has the same color, then aggregate:

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

This produces one record per series of adjance orange records for each user:

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

If you just want the longest streak per user, you can use aggregation on top of this, or window functions again:

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

Demo on DB Fiddle

SELECT t1.user_id, MAX(1 + DATEDIFF(t2.`timestamp`, t1.`timestamp`)) max_delta
FROM survey_daily t1
JOIN survey_daily t2 ON t1.user_id = t2.user_id
WHERE t1.color = 'ORANGE'
  AND t2.color = 'ORANGE'
  AND t1.`timestamp` <= t2.`timestamp`
  AND NOT EXISTS ( SELECT NULL
                   FROM survey_daily t3
                   WHERE t1.user_id = t3.user_id
                     AND t3.color != 'ORANGE'
                     AND t1.`timestamp` < t3.`timestamp`
                     AND t3.`timestamp` < t2.`timestamp` )
GROUP BY t1.user_id;

Logic. Take all record pairs for user where the color is ORANGE for both records and none record with another color exists between them. Calculate the distance in days in each pair. Get maximal gap value.

fiddle (thanks to GMB for a fiddle which source data scripts are taken from).

PS. If none record with ORANGE color exists for some user then this user will not be returned. If you need in such users too then get a copy of survey_daily table, LEFT JOIN my query to it as a subquery by user_id, then get users from the table and the amount of consecutive days from the subquery (wrap it with COALESCE function for to convern NULL value to zero).