gistfile1.txt

SET @frameworks = 'raptor,browsertime,talos,awsy';

select
    perf_summary.revision,
    job_type.name
FROM (
  select
    distinct push.revision as revision,
    summary.push_id as push_id,
    summary.repository_id as repository_id,
    summary.id as id
  from
    performance_alert_summary as summary
    INNER JOIN performance_alert as alert ON (summary.id=alert.summary_id or summary.id=related_summary_id)
    INNER JOIN push as push ON push.id = summary.push_id
	INNER JOIN performance_signature AS signature ON signature.id = alert.series_signature_id
	INNER JOIN performance_framework AS framework ON framework.id = signature.framework_id
  where
	FIND_IN_SET(framework.name, @frameworks)
    AND (summary.notes IS NULL OR summary.notes NOT LIKE '%#harness%') -- ignore harness updates
    AND (summary.notes IS NULL OR summary.notes NOT LIKE '%#infra%') -- ignore false alerts caused by infra changes
	AND summary.created >= "2020-03-01"
	AND summary.status in (4,7,8)
) AS perf_summary
INNER JOIN performance_alert as alert ON (perf_summary.id=alert.summary_id or perf_summary.id=related_summary_id)
INNER JOIN performance_datum as datum ON (datum.push_id = perf_summary.push_id and datum.repository_id=perf_summary.repository_id and datum.signature_id = alert.series_signature_id)
INNER JOIN job as job on job.id = datum.job_id
INNER JOIN job_type as job_type on job_type.id = job.job_type_id
GROUP BY job_type.name