Find the longest consecutive failure streak per suite
Return every non-NULL suite with its longest adjacent FAIL streak in ascending ID order within that suite. Status is PASS or FAIL; suites with no failures return zero. Return only the requested columns and follow the specified ordering. This question probes a cumulative count of passes labels failure blocks; aggregate each block and then take the suite maximum.
Input
Setup script defining results(id INT, suite VARCHAR(20), status VARCHAR(10)).
Output
suite, longest_failure_streak; ascending suite.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE results(id INT, suite VARCHAR(20), status VARCHAR(10)); INSERT INTO results VALUES (1,'API','FAIL'),(2,'UI','PASS'),(3,'API','FAIL'),(4,'API','PASS'),(5,'API','FAIL'),(6,'UI','PASS');
Expected result
suite | longest_failure_streak API | 2 UI | 0
Another dataset (1)
CREATE TABLE results(id INT, suite VARCHAR(20), status VARCHAR(10)); INSERT INTO results VALUES (1,'A','PASS'),(2,'B','FAIL'),(3,'B','FAIL'),(4,'B','FAIL');
Expected result
suite | longest_failure_streak A | 0 B | 3
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Each PASS starts a new failure block.
- Use a cumulative conditional SUM then two levels of aggregation.
Target complexity: time O(n log n) · space O(n)