MySQL 8.0

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

CREATE TABLE jobs (job_name CHAR(1), created_on DATE, result VARCHAR(8));
INSERT INTO jobs VALUES
('x', '2021-01-01', 'fail'),       -- 1 consecutive
('x', '2021-01-02', 'success'),
('x', '2021-01-03', 'success'),
('x', '2021-01-04', 'fail'),       -- 2 consecutive
('x', '2021-01-05', 'fail'),
('x', '2021-01-06', 'success'),
('x', '2021-01-07', 'fail'),       -- 3 consecutive
('x', '2021-01-08', 'fail'),
('x', '2021-01-09', 'fail'),
('x', '2021-01-10', 'success'),
('x', '2021-01-11', 'success'),
('x', '2021-01-12', 'success'),
('x', '2021-01-13', 'success'),
('x', '2021-01-14', 'fail'),       -- 4 consecutive
('x', '2021-01-15', 'fail'),
('x', '2021-01-16', 'fail'),
('x', '2021-01-17', 'fail');

Records: 17  Duplicates: 0  Warnings: 0


-- Divide to groups
WITH 
cte1 AS ( SELECT *,
                 SUM(result = 'success') OVER (PARTITION BY job_name ORDER BY created_on) grp
          FROM jobs )
SELECT * FROM cte1 ORDER BY job_name, created_on;


job_name
created_on
result
grp


x
2021-01-01
fail
0

x
2021-01-02
success
1

x
2021-01-03
success
2

x
2021-01-04
fail
2

x
2021-01-05
fail
2

x
2021-01-06
success
3

x
2021-01-07
fail
3

x
2021-01-08
fail
3

x
2021-01-09
fail
3

x
2021-01-10
success
4

x
2021-01-11
success
5

x
2021-01-12
success
6

x
2021-01-13
success
7

x
2021-01-14
fail
7

x
2021-01-15
fail
7

x
2021-01-16
fail
7

x
2021-01-17
fail
7


-- Divide to groups and count the amount of 'fail' values per group
WITH 
cte1 AS ( SELECT *,
                 SUM(result = 'success') OVER (PARTITION BY job_name ORDER BY created_on) grp
          FROM jobs ),
cte2 AS ( SELECT job_name, grp, COUNT(*) cnt
          FROM cte1
          WHERE result = 'fail'
          GROUP BY job_name, grp
       /* HAVING cnt >= 2 */ ) 
SELECT * FROM cte2 ORDER BY job_name, grp;


job_name
grp
cnt


x
0
1

x
2
2

x
3
3

x
7
4


WITH 
cte1 AS ( SELECT *,
                 SUM(result = 'success') OVER (PARTITION BY job_name ORDER BY created_on) grp
          FROM jobs ),
cte2 AS ( SELECT job_name, grp, COUNT(*) cnt
          FROM cte1
          WHERE result = 'fail'
          GROUP BY job_name, grp
          HAVING cnt >= 2 )    -- 2 or more
SELECT cte1.*
FROM cte1
JOIN cte2 USING (job_name, grp)
WHERE cte1.result = 'fail';


job_name
created_on
result
grp


x
2021-01-04
fail
2

x
2021-01-05
fail
2

x
2021-01-07
fail
3

x
2021-01-08
fail
3

x
2021-01-09
fail
3

x
2021-01-14
fail
7

x
2021-01-15
fail
7

x
2021-01-16
fail
7

x
2021-01-17
fail
7


WITH 
cte1 AS ( SELECT *,
                 SUM(result = 'success') OVER (PARTITION BY job_name ORDER BY created_on) grp
          FROM jobs ),
cte2 AS ( SELECT job_name, grp, COUNT(*) cnt
          FROM cte1
          WHERE result = 'fail'
          GROUP BY job_name, grp
          HAVING cnt BETWEEN 2 AND 3 )    -- 2 or 3
SELECT cte1.*
FROM cte1
JOIN cte2 USING (job_name, grp)
WHERE cte1.result = 'fail';


job_name
created_on
result
grp


x
2021-01-04
fail
2

x
2021-01-05
fail
2

x
2021-01-07
fail
3

x
2021-01-08
fail
3

x
2021-01-09
fail
3


WITH 
cte1 AS ( SELECT *,
                 SUM(result = 'success') OVER (PARTITION BY job_name ORDER BY created_on) grp
          FROM jobs ),
cte2 AS ( SELECT job_name, grp, COUNT(*) cnt
          FROM cte1
          WHERE result = 'fail'
          GROUP BY job_name, grp
          HAVING cnt = 3 )    -- strictly 3
SELECT cte1.*
FROM cte1
JOIN cte2 USING (job_name, grp)
WHERE cte1.result = 'fail';


job_name
created_on
result
grp


x
2021-01-07
fail
3

x
2021-01-08
fail
3

x
2021-01-09
fail
3

job_name	created_on	result	grp
x	2021-01-01	fail	0
x	2021-01-02	success	1
x	2021-01-03	success	2
x	2021-01-04	fail	2
x	2021-01-05	fail	2
x	2021-01-06	success	3
x	2021-01-07	fail	3
x	2021-01-08	fail	3
x	2021-01-09	fail	3
x	2021-01-10	success	4
x	2021-01-11	success	5
x	2021-01-12	success	6
x	2021-01-13	success	7
x	2021-01-14	fail	7
x	2021-01-15	fail	7
x	2021-01-16	fail	7
x	2021-01-17	fail	7