SELECT test_name, count() AS c, max(check_start_time) AS last_time, argMin(commit_sha, check_start_time) AS first_seen, argMax(commit_sha, check_start_time) AS last_seen FROM `gh-data`.checks
WHERE
    test_status = 'FAIL'
    AND check_name LIKE 'Functional%'
    AND pull_request_number = 0
    AND check_start_time >= now() - INTERVAL 100 DAY
GROUP BY test_name
HAVING last_time >= now() - INTERVAL 3 DAY
ORDER BY c DESC
LIMIT 100