1#standardSQL 2WITH calibration AS ( 3 SELECT 4 RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary, 5 REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy, 6 job_name, 7 build_id 8 FROM ( 9 SELECT 10 REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name, 11 test_name, 12 job_name, 13 build_id, 14 timestamp 15 FROM 16 `grpc-testing.jenkins_test_results.aggregate_results` 17 WHERE 18 timestamp > TIMESTAMP(DATETIME("{calibration_begin} 00:00:00", "America/Los_Angeles")) 19 AND timestamp <= TIMESTAMP(DATETIME("{calibration_end} 23:59:59", "America/Los_Angeles")) 20 AND NOT REGEXP_CONTAINS(job_name, 21 'portability') 22 AND result != 'PASSED' 23 AND result != 'SKIPPED' )), 24 reporting AS ( 25 SELECT 26 RTRIM(LTRIM(REGEXP_REPLACE(filtered_test_name, r'(/\d+)|(bins/.+/)|(cmake/.+/.+/)', ''))) AS test_binary, 27 REGEXP_EXTRACT(test_name, r'GRPC_POLL_STRATEGY=(\w+)') AS poll_strategy, 28 job_name, 29 build_id, 30 timestamp 31 FROM ( 32 SELECT 33 REGEXP_REPLACE(test_name, r'(/\d+)|(GRPC_POLL_STRATEGY=.+)', '') AS filtered_test_name, 34 test_name, 35 job_name, 36 build_id, 37 timestamp 38 FROM 39 `grpc-testing.jenkins_test_results.aggregate_results` 40 WHERE 41 timestamp > TIMESTAMP(DATETIME("{reporting_begin} 00:00:00", "America/Los_Angeles")) 42 AND timestamp <= TIMESTAMP(DATETIME("{reporting_end} 23:59:59", "America/Los_Angeles")) 43 AND NOT REGEXP_CONTAINS(job_name, 44 'portability') 45 AND result != 'PASSED' 46 AND result != 'SKIPPED' )) 47SELECT 48 reporting.test_binary, 49 reporting.poll_strategy, 50 reporting.job_name, 51 reporting.build_id, 52 STRING(reporting.timestamp, "America/Los_Angeles") as timestamp_MTV 53FROM 54 reporting 55LEFT JOIN 56 calibration 57ON 58 reporting.test_binary = calibration.test_binary 59WHERE 60 calibration.test_binary IS NULL 61ORDER BY 62 timestamp DESC; 63