MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table jobs as
     select 1 as id, 'x' as job_name, date('2021-01-20') as created_on, 'fail' as result union all
     select 2 as id, 'y' as job_name, date('2021-01-01') as created_on, 'success' as result union all
     select 3 as id, 'y' as job_name, date('2021-01-02') as created_on, 'success' as result union all
     select 4 as id, 'x' as job_name, date('2021-01-22') as created_on, 'fail' as result union all
     select 5 as id, 'x' as job_name, date('2021-01-23') as created_on, 'fail' as result union all
     select 6 as id, 'z' as job_name, date('2021-03-15') as created_on, 'fail' as result union all
     select 7 as id, 'z' as job_name, date('2021-03-11') as created_on, 'success' as result union all
     select 8 as id, 'z' as job_name, date('2021-03-16') as created_on, 'fail' as result union all
     select 9 as id, 'x' as job_name, date('2021-01-18') as created_on, 'fail' as result union all
     select 10 as id, 'z' as job_name, date('2021-01-03') as created_on, 'fail' as result union all
     select 11 as id, 'x' as job_name, date('2021-01-19') as created_on, 'success' as result

Records: 11  Duplicates: 0  Warnings: 0


SELECT j2.job_name, max(j2.created_on) mdate 
FROM jobs j2 
WHERE j2.result = 'success' 
GROUP BY j2.job_name


job_name
mdate


y
2021-01-02

z
2021-03-11

x
2021-01-19


WITH last_success_date AS (
  SELECT j2.job_name, max(j2.created_on) mdate FROM jobs j2
    WHERE j2.result = 'success' 
    GROUP BY j2.job_name
) 
SELECT j.* FROM jobs j
LEFT JOIN last_success_date lsd ON j.job_name = lsd.job_name
WHERE j.result = 'fail' AND j.created_on > lsd.mdate


id
job_name
created_on
result


1
x
2021-01-20
fail

4
x
2021-01-22
fail

5
x
2021-01-23
fail

6
z
2021-03-15
fail

8
z
2021-03-16
fail


WITH failed_jobs AS (
  WITH last_success_date AS (
    SELECT j2.job_name, max(j2.created_on) mdate FROM jobs j2
      WHERE j2.result = 'success' 
      GROUP BY j2.job_name
  ) 
  SELECT j.* FROM jobs j
  LEFT JOIN last_success_date lsd ON j.job_name = lsd.job_name
  WHERE j.result = 'fail' AND j.created_on > lsd.mdate
) 
SELECT fj.* FROM failed_jobs fj
LEFT JOIN (
  SELECT fj2.job_name, count(fj2.id) fail_count FROM failed_jobs fj2
  GROUP BY  fj2.job_name
) AS with_counts on with_counts.job_name = fj.job_name 
WHERE fail_count >= 2 ORDER BY fj.created_on DESC


id
job_name
created_on
result


8
z
2021-03-16
fail

6
z
2021-03-15
fail

5
x
2021-01-23
fail

4
x
2021-01-22
fail

1
x
2021-01-20
fail
id	job_name	created_on	result
1	x	2021-01-20	fail
4	x	2021-01-22	fail
5	x	2021-01-23	fail
6	z	2021-03-15	fail
8	z	2021-03-16	fail