text: -- This query updates the 'P50 Slow Tests' and 'P90 Slow Tests' metrics in the cq_builder_metrics_day
  -- table. It uses an interval of the last 2 days so that there is some redundancy if the job fails
  -- Slow tests are defined as tests that take longer than 5 minutes to run at P50, or 10m to run at P90.
  -- We also exclude any tests that have fewer than 10 runs to remove one-off test runs like
  -- https://crrev.com/c/2545074/13
  -- This query is meant to be almost identical to the one in cq_builder_metrics_week_slow_tests.sql.
  
  -- The lines below are used by the deploy tool.
  --name: Populate cq_builder_metrics_day slow test metrics
  --schedule: every 4 hours synchronized
  
  DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE('PST8PDT'), INTERVAL 2 DAY);
  -- This isn't really needed, but useful to have around when doing backfills
  -- The end_date is exclusive, so if it's on a Monday then that week won't be
  -- included. This is why we add a day to it.
  DECLARE end_date DATE DEFAULT DATE_ADD(CURRENT_DATE('PST8PDT'), INTERVAL 1 DAY);
  DECLARE start_ts TIMESTAMP DEFAULT TIMESTAMP(start_date, 'PST8PDT');
  DECLARE end_ts TIMESTAMP DEFAULT TIMESTAMP(end_date, 'PST8PDT');
  DECLARE slow_p50_cutoff INT64 DEFAULT 300;
  DECLARE slow_p90_cutoff INT64 DEFAULT 600;
  
  -- Merge statement
  MERGE INTO
    `chrome-trooper-analytics.metrics.cq_builder_metrics_day` AS T
  USING
    (
    WITH builds AS (
      SELECT
        EXTRACT(DATE FROM b.start_time AT TIME ZONE "PST8PDT") AS `date`,
        b.id,
        b.builder.builder,
        b.start_time,
        b.infra.swarming.task_id
      FROM
        `cr-buildbucket.chromium.builds` b,
        `chrome-trooper-analytics.metrics.cq_builders` cq
      WHERE
        -- As we bucket the build using start_date, we need to include any builds
        -- that were created on the previous day.
        b.create_time >= TIMESTAMP_SUB(start_ts, INTERVAL 1 DAY)
        AND b.create_time < end_ts
        AND b.builder.bucket = 'try'
        AND b.builder.project = 'chromium'
        AND b.builder.builder = cq.builder
      ),
      test_tasks AS (
      SELECT
        b.id,
        ANY_VALUE(b.date) date,
        ANY_VALUE(b.builder) builder,
        ANY_VALUE(b.start_time) builder_start_time,
        (
          SELECT SUBSTR(i, 12) FROM t.request.tags i WHERE i LIKE 'test_suite:%'
        ) test_name,
        MAX(TIMESTAMP_DIFF(t.start_time, t.create_time, SECOND)) max_shard_pending,
        MAX(TIMESTAMP_DIFF(t.end_time, t.start_time, SECOND)) max_shard_runtime
      FROM
        `chromium-swarm.swarming.task_results_summary` t,
        builds b
      WHERE
        b.date >= start_date AND b.date < end_date
        AND t.end_time >= TIMESTAMP_SUB(start_ts, INTERVAL 1 DAY)
        AND t.end_time < TIMESTAMP_ADD(end_ts, INTERVAL 1 DAY)
        AND t.request.parent_task_id = b.task_id
      GROUP BY id, test_name
      ),
      test_metrics AS (
      SELECT
        t.date,
        t.builder,
        MIN(t.builder_start_time) min_builder_start_time,
        MAX(t.builder_start_time) max_builder_start_time,
        t.test_name,
        APPROX_QUANTILES(t.max_shard_pending, 100) max_shard_pending,
        APPROX_QUANTILES(t.max_shard_runtime, 100) max_shard_runtime,
        count(id) num_runs,
        countif(t.max_shard_runtime > slow_p50_cutoff) num_slow_runs,
      FROM test_tasks t
      WHERE t.test_name is not null
      GROUP BY t.date, t.builder, t.test_name
      HAVING num_runs > 10
      ),
      slow_test_metrics AS (
      SELECT *
      FROM test_metrics
      WHERE (max_shard_runtime[OFFSET(50)] > slow_p50_cutoff or max_shard_runtime[OFFSET(90)] > slow_p90_cutoff)
      )
    SELECT
      date,
      'Count Tests' AS metric,
      builder,
      MAX(max_builder_start_time) AS max_builder_start_time,
      ARRAY_AGG(
        STRUCT(test_name AS label, CAST(num_runs AS NUMERIC) AS value)
        ORDER BY test_name
      ) AS value_agg,
    FROM test_metrics
    GROUP BY date, builder
    UNION ALL
    SELECT
      date,
      'P50 Slow Tests' AS metric,
      builder,
      MAX(max_builder_start_time) max_builder_start_time,
      ARRAY_AGG(
        STRUCT(test_name AS label, CAST(max_shard_runtime[OFFSET(50)] AS NUMERIC) AS value)
        ORDER BY test_name
      ) AS value_agg,
    FROM slow_test_metrics
    GROUP BY date, builder
    UNION ALL
    SELECT
      date,
      'P90 Slow Tests' AS metric,
      builder,
      MAX(max_builder_start_time) AS max_builder_start_time,
      ARRAY_AGG(
        STRUCT(test_name AS label, CAST(max_shard_runtime[OFFSET(90)] AS NUMERIC) AS value)
        ORDER BY test_name
      ) AS value_agg,
    FROM slow_test_metrics
    GROUP BY date, builder
    ) S
  ON
    T.date = S.date AND T.metric = S.metric AND T.builder = S.builder
    WHEN MATCHED AND T.checkpoint != string(S.max_builder_start_time, "UTC") THEN
      UPDATE SET value_agg = S.value_agg, checkpoint = string(S.max_builder_start_time, "UTC"), last_updated = current_timestamp()
    WHEN NOT MATCHED THEN
      INSERT (date, metric, builder, value_agg, last_updated, checkpoint)
      VALUES (date, metric, builder, value_agg, current_timestamp(), string(max_builder_start_time, "UTC"));
  