appengine/findit/services/flake_detection/flaky_attempts_by_failure_type.cq_false_rejection.sql

# To query cq-attempt-level false CQ rejections in the past 7 days up to NOW.
#
# False CQ rejection:
# For the same CL/patch, "Submit to CQ" was manually clicked 2+ times to get the
# CL submitted, and those failed CQ attempts are false rejections.
#
# To be more inclusive for analysis:
# 1. This query includes data of CQ "Dry run".
# 2. The concept of equivalent patchset group is introduced. For the same CL,
#    two patchsets are equivalent if there is no code change between them. In
#    other words, Gerrit shows no code diff between the two patchsets. This
#    could happen in the following cases:
#    * A new patchset is generated due to editing the commit message.
#    * A trivial rebase: the files touched by the CL are not changed between the
#      two base commits of the two patchsets.
#      Example: https://chromium-review.googlesource.com/c/chromium/src/+/1000000/2..3
#    This is represented by the cq_attempts.earliest_equivalent_patchset.

WITH
  # Group CQ attempts by cq_name/CL/earliest_equivalent_patchset.
  patch_groups AS (
  SELECT
    cq_name,
    issue,
    (CASE
        WHEN earliest_equivalent_patchset IS NOT NULL THEN
          # Integer type. Added on April 5, 2018.
          CAST(earliest_equivalent_patchset AS STRING)
        ELSE patchset  # String type.
      END) AS patchset_group_id,
    ARRAY_AGG(STRUCT(patchset, failed, fail_type)) AS attempts,
    LOGICAL_OR(NOT failed) AS has_success,
    COUNT(*) AS total_attempts
  FROM
    `chrome-infra-events.aggregated.cq_attempts`
  WHERE
    attempt_start_msec >= UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 day))
    # Below is for debug reference.
    # AND attempt_start_msec >= UNIX_MILLIS(TIMESTAMP('2018-04-24')) # To test in a time range.
    # AND cq_name = 'chromium/chromium/src'  # To look at a specific commit queue.
    # AND not dry_run  # To exclude CQ dry runs.
    # AND issue = '919044'  # To test against a Chromium CL.
  GROUP BY
    cq_name,
    issue,
    patchset_group_id ),

  # Record the total attempts in each queue.
  total_attempts_by_queue AS (
  SELECT
    cq_name,
    SUM(total_attempts) AS total_attempts
  FROM
    patch_groups
  GROUP BY
    cq_name ),

  # Count false rejections by cq_name/fail_type.
  false_rejections AS (
  SELECT
    cq_name,
    a.fail_type,
    COUNT(*) AS rejected_num
  FROM
    patch_groups AS pg,
    pg.attempts AS a
  WHERE
    # A false rejection is a failed attempt that has matching successful one.
    pg.has_success
    AND a.failed
    # To exclude non-infra-related or non-test-related failed attempts.
    AND a.fail_type NOT IN ('NOT_LGTM',
      'OPEN_DEPENDENCY',
      'MANUAL_CANCEL',
      'NO_SIGNCLA',
      'FAILED_SIGNCLA_REQUEST',
      'MISSING_LGTM',
      'RETRY_QUOTA_EXCEEDED')
  GROUP BY
    cq_name,
    fail_type )

SELECT
  fr.cq_name,
  fr.fail_type,
  FORMAT('%.2f%%', SAFE_DIVIDE(fr.rejected_num, tabq.total_attempts) * 100) AS percentage,
  tabq.total_attempts,
  fr.rejected_num
FROM
  false_rejections AS fr
INNER JOIN
  total_attempts_by_queue AS tabq
ON
  fr.cq_name = tabq.cq_name
ORDER BY
  cq_name ASC,
  percentage DESC