Audit release coverage before comparing regressions

FoundationsIntermediate

Audit release coverage before comparing regressions

Do product and API events cover the same releases well enough for a regression comparison?

Build a shared release spine before calculating rates so missing instrumentation remains null instead of looking like perfect reliability.

Read-only SQL

WITH releases AS (
  SELECT release FROM product_events
  UNION
  SELECT release FROM api_requests
),
product_coverage AS (
  SELECT
    release,
    COUNT(*) AS product_events,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)
      AS successful_product_events
  FROM product_events
  GROUP BY release
),
api_coverage AS (
  SELECT
    release,
    COUNT(*) AS api_requests,
    SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS api_errors
  FROM api_requests
  GROUP BY release
)
SELECT
  releases.release,
  COALESCE(product.product_events, 0) AS product_events,
  COALESCE(product.successful_product_events, 0)
    AS successful_product_events,
  COALESCE(api.api_requests, 0) AS api_requests,
  COALESCE(api.api_errors, 0) AS api_errors,
  ROUND(
    100.0 * api.api_errors / NULLIF(api.api_requests, 0),
    1
  ) AS api_error_pct
FROM releases
LEFT JOIN product_coverage AS product
  ON product.release = releases.release
LEFT JOIN api_coverage AS api
  ON api.release = releases.release
ORDER BY releases.release;

The dataset and query remain in this browser.

Published expected result

API request coverage by release

Chart value

release	product_events	successful_product_events	api_requests	api_errors	api_error_pct
2026.07.1	13	12	0	0	—
2026.07.2	8	8	10	6	60

How to read the query

The release spine includes versions present in either source instead of starting from whichever table happens to have rows.
A release with no API requests keeps a null error rate, preventing absent coverage from appearing as zero-percent errors.
Product event counts and API request counts expose whether the populations are comparable before a regression claim is made.

Decisions the SQL cannot make

1Define which services and clients share a meaningful release identifier.
2Require minimum coverage and complete observation windows before comparing rates.
3Investigate instrumentation changes separately from product regressions.

Continue from this lesson

Feature rollout analysis Schema adoption recipe API reliability by release