timestamp           status      Servcie  
2024-06-20 11:00    ERROR       S1  
2024-06-20 11:02    Success     S1  
2024-06-20 11:03    ERROR       S1  
2024-06-20 11:04    ERROR       S1  
2024-06-20 11:05    ERROR       S1 
2024-06-20 11:06    ERROR       S1  
2024-06-20 11:07    ERROR       S1
   
2024-06-20 11:00    ERROR       S2  
2024-06-20 11:01    ERROR       S2

2024-06-20 11:01    ERROR       S3  
2024-06-20 11:02    ERROR       S3  
2024-06-20 11:03    ERROR       S3  
2024-06-20 11:04    ERROR       S3  
2024-06-20 11:05    ERROR       S3 
2024-06-20 11:06    Success     S3

Results:
name    ErrorCount   FirstFailedTimestamp    LastFailedTimestamp
S1      5            2024-06-20 11:03        2024-06-20 11:07
S2      2            2024-06-20 11:00        2024-06-20 11:01
S3      0

ErrorCount –> number of ERROR count. If we have Success status inbetween, consider errorcount after “Success” status.
If last status is success then, ErrorCount is 0.
FirstFailedTimestamp –> timestamp First error or After “Success” status record
LastFailedTimestamp –> Last Timestamp of Error.

I tried below query , but not working

WITH data AS (
    SELECT TIMESTAMP("2024-06-20 11:00:00") AS timestamp, "ERROR" AS status, "S1" AS service UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "SUCCESS", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "ERROR", "S1" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:07:00"), "ERROR", "S1" UNION ALL
   
    SELECT TIMESTAMP("2024-06-20 11:00:00"), "ERROR", "S2" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S2" UNION ALL
    
    SELECT TIMESTAMP("2024-06-20 11:01:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:02:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:03:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:04:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:05:00"), "ERROR", "S3" UNION ALL
    SELECT TIMESTAMP("2024-06-20 11:06:00"), "SUCCESS", "S3"
)

SELECT
    service AS name,
    CASE WHEN MAX(status) = 'SUCCESS' THEN 0 ELSE COUNTIF(status = 'ERROR' AND SuccessFlag = 1) END AS ErrorCount,
    MIN(IF(status = 'ERROR' AND SuccessFlag = 1, FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp), NULL)) AS FirstFailedTimestamp,
    MAX(IF(status = 'ERROR', FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp), NULL)) AS LastFailedTimestamp
FROM (
    SELECT
        service,
        timestamp,
        status,
        MAX(IF(status = 'SUCCESS', 1, 0)) OVER (PARTITION BY service ORDER BY timestamp DESC) AS SuccessFlag
    FROM
        data
)
GROUP BY
    service