| #standardSQL |
| WITH calibration AS ( |
| SELECT |
| RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary, |
| REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy, |
| job_name, |
| build_id |
| FROM ( |
| SELECT |
| REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name, |
| test_name, |
| job_name, |
| build_id, |
| timestamp |
| FROM |
| `grpc-testing.jenkins_test_results.aggregate_results` |
| WHERE |
| timestamp > TIMESTAMP(DATETIME("{calibration_begin} 00:00:00", "America/Los_Angeles")) |
| AND timestamp <= TIMESTAMP(DATETIME("{calibration_end} 23:59:59", "America/Los_Angeles")) |
| AND NOT REGEXP_CONTAINS(job_name, |
| 'portability') |
| AND result != 'PASSED' |
| AND result != 'SKIPPED' )), |
| reporting AS ( |
| SELECT |
| RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary, |
| REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy, |
| job_name, |
| build_id, |
| timestamp |
| FROM ( |
| SELECT |
| REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name, |
| test_name, |
| job_name, |
| build_id, |
| timestamp |
| FROM |
| `grpc-testing.jenkins_test_results.aggregate_results` |
| WHERE |
| timestamp > TIMESTAMP(DATETIME("{reporting_begin} 00:00:00", "America/Los_Angeles")) |
| AND timestamp <= TIMESTAMP(DATETIME("{reporting_end} 23:59:59", "America/Los_Angeles")) |
| AND NOT REGEXP_CONTAINS(job_name, |
| 'portability') |
| AND result != 'PASSED' |
| AND result != 'SKIPPED' )) |
| SELECT |
| reporting.test_binary, |
| reporting.poll_strategy, |
| reporting.job_name, |
| reporting.build_id, |
| STRING(reporting.timestamp, "America/Los_Angeles") as timestamp_MTV |
| FROM |
| reporting |
| LEFT JOIN |
| calibration |
| ON |
| reporting.test_binary = calibration.test_binary |
| WHERE |
| calibration.test_binary IS NULL |
| ORDER BY |
| timestamp DESC; |