Quality Metrics and Test Reporting Questions

Direct answer

An escaped defect is a confirmed defect (not a feature request or duplicate) that was present in a released build and was first found after release, normally in production. Attribute it to the release that introduced it, not the release in which it was found, and compute the rate per release as escapes divided by all defects attributed to that release. This works only if a handful of tags are recorded consistently at close time.

Structured elaboration

Definition and attribution

• Escape: first found in production (or, if you also measure stage leakage, found in any stage after the one that should have caught it).
• Attribution: the fix commit (the code change that repaired the bug) shows which lines were changed to repair it; looking up the history of those lines (for example git blame) usually names the commit that introduced the bug, though it is a heuristic that refactors, reformatting or a fix that only adds missing code can mislead, so confirm by reading the change, and the release table tells you which release shipped that commit. If the history is unclear, a bisect (a search that tests commits to find the first bad one) does the same job. Record it during triage (reviewing and classifying a new bug report), not when the ticket is filed. A config change is attributed the same way: the release is whichever deployment or config push first put the bad setting into that environment.
• Rate: escapes in release N divided by all defects introduced in release N, as a percentage.
• Stage attribution: record which stage should have caught it first (unit tests, integration tests, code review, QA, staging).

Minimum tags that must be consistent

Worked example

Illustrative data. Release 7 introduced 10 confirmed defects: 4 found in development or CI (continuous integration, the automated build-and-test pipeline), 3 found in QA, 3 found in production.

• Escape rate = 3 / 10 = 30 percent.
• By severity: 5 of the 10 defects were severity 1 or 2, and 2 of those escaped, so the serious-defect escape rate is 2 / 5 = 40 percent. Reporting both stops a cosmetic-heavy release from hiding serious escapes.
• Stage attribution for the 3 escapes: one missed by integration tests (no test covered the flow), one by QA (staging data differed from production), one by code review.

Filled-in example for one defect, so you can see the tags on a real ticket:

The 5 severity 1 or 2 defects in release 7 are 5 rows like this one, and 2 of the 5 have Environment first found = production, which is where the 2 / 5 comes from.

Environment case: a bug appears in production only after a config push at release 7's deploy. Attribute it to release 7, tag the stage as "staging config differs", and note that the trigger was configuration, not code.

Restatement: a production defect found during release 8 is traced to a commit from release 6. Release 6 had 8 defects and 1 escape (1 / 8 = 12.5 percent). It now has 9 and 2, so 2 / 9 = 22.2 percent. This is why rates are reported "as of" a date and older releases are restated.

Trade-offs and pitfalls

• Gaming: production bugs relabelled as enhancements, severities downgraded, reports delayed. Guard with triage-owned severity, a monthly audit of ten closed tickets, and a reconciliation of support tickets against defects.
• Free text: use required dropdowns at close instead.
• Equal age: compare releases at equal age, since recent releases have not had time for escapes to surface.
• Scope: the rate measures the safety net, not a person; do not use it to compare individuals.

Direct answer

Capture a small set of facts as events (a defect was created, a test ran, a build was released) and stamp every one with the same identifiers: a stable test id, a commit, a release, a component, and a severity from one shared scale. Detection rate and escape rate are then simple counts over joined tables. Keep the bug tracker as the system of record for defects, copy its events into a queryable store, and keep per-test results in an append-only table. For a typical team start with PostgreSQL and move test results to a columnar warehouse only when history queries stop being interactive.

Terms. Append-only means rows are only added, never edited. Partitioned by date means stored in separate chunks per day so queries touching a date range read less. A columnar store keeps data column by column, which is fast for scanning long history. A system of record is the one place that is the official truth for a kind of data. A webhook is a message the tracker sends to your system automatically when something changes. A failure signature is a short fingerprint of an error message so equal failures group together. Event time is when something happened; ingest time is when your system loaded it.

Metric definitions (so the events can support them)

Detection rate is the share of a release's defects that testing found before customers did. Escape rate is the share that reached production (an "escape").

$\text{detection rate} = \frac{\text{defects found before release}}{\text{defects found before release} + \text{escaped defects}}$

$\text{escape rate} = \frac{\text{escaped defects}}{\text{defects found before release} + \text{escaped defects}}$

Plain reading: both use the same denominator (all defects attributed to that release), so they always add to 100%. Example: 90 found before release and 10 in production gives 90% detection and 10% escape.

Events and attributes

What must be tagged consistently to compute escape rate

1. found_in must be filled for every defect, with a closed list of values. A blank is counted as neither found nor escaped and silently inflates detection. Example: 90 defects found before release and 10 in production, but 10 more have a blank found_in that are really production escapes. Reported detection is 90 / (90 + 10) = 90%, while the true value is 90 / 110 = 82%, and the reported escape rate is 10% against a true 20 / 110 = 18%.
2. Every defect needs the release it belongs to. Without it there is no denominator per release.
3. One severity scale, and a log of severity changes, so that "escape of a critical" is comparable across teams and over time.
4. Duplicates must point at their original (duplicate_of) so one problem is not counted three times.
5. Measure escapes at a fixed age (say 30 days after release), otherwise old releases look worse than new ones purely because they had longer to be found.

Per-test failure history: a worked example

A test checkout_pay_by_card ran 20 times last week across 12 commits. It failed 6 times; on 5 of those failures the same commit passed on retry. Same commit, both outcomes: strong evidence of a flaky test, so the record needs commit sha and attempt number, otherwise retries erase the evidence. Only 1 failure has no passing retry, and it deserves a real look.

How one defect is tagged and counted. Defect D-101 is introduced in release 4.1 and found by a customer during 4.2. Its row reads introduced_in_release = 4.1, found_in_release = 4.2, found_in = production. It counts as an escape for release 4.1 (where it was introduced), and the gap between the two releases is its time to detect. Defect D-102 is introduced and caught in 4.2 by a test in staging: introduced_in_release = found_in_release = 4.2, found_in = staging, counted as detected.

Storage choice and why

• Defects: low volume, relational, edited over time. Keep them in the tracker and mirror through its API or webhooks into PostgreSQL tables so you can join them with results.
• Test results: high volume, never edited. Append-only rows, partitioned by date, keyed by test id. PostgreSQL is fine at first; a columnar store fits once you query years of history. ClickHouse, BigQuery and Snowflake are interchangeable examples of that category, so you do not need to learn all three, and this is an optional later step, not a starting requirement.
• Not a good fit: a spreadsheet or the tracker's own reports for per-test history, because they cannot join defects to runs.

Pitfalls

• Rename a test file and the history resets unless the id is stable.
• Recording only the final result of a retried test hides flakiness.
• Storing raw logs with secrets or personal data in the results table. Keep a failure signature and link to logs elsewhere.
• Event time versus ingest time. Report on when it happened, not when it was loaded.

Direct answer

Grouping failures is a clustering problem (putting similar items into the same group). Core versus advanced: the core of a good interview answer is stages 1 and 2 (normalize, then exact-match on a hash of the result), plus the precision versus recall trade-off. Stages 3 and 4 (MinHash, LSH, cosine verification, union-find) are only needed to catch near-duplicates at this 100,000-signature scale, and interviewers rarely expect you to derive them.

With about 100,000 distinct failure signatures you cannot compare every pair, so use four stages: (1) normalize each failure into a stable form (strip timestamps, ids, addresses, numbers, build paths) with one small parser per language; (2) exact-match on a hash of the normalized text (a hash turns the text into a short fixed-size code, so identical text always gives the identical code and can be looked up instantly), which already collapses most volume; (3) propose near-duplicate candidates cheaply with MinHash and locality-sensitive hashing (LSH, a trick that makes similar items land in the same bucket, meaning a lookup slot, so you only compare items that share a bucket); (4) verify each candidate with a similarity score (TF-IDF cosine) against a threshold and merge with union-find (a structure that keeps track of which items already belong to the same group, so merging two groups is one cheap step). Choose the threshold from labelled examples, and version everything so that format drift does not silently split old groups from new.

Why not compare everything

All pairs of 100,000 signatures is 100,000 x 99,999 / 2 = 4,999,950,000, about 5 billion comparisons. LSH cuts that to the pairs that collide in at least one bucket.

The stages in detail

• Feature extraction: for each language keep the parts that identify a cause: exception type (KeyError, java.net.SocketTimeoutException, AssertionError), the message with volatile parts replaced by tokens, and the top few in-repo stack frames (drop library and test-runner frames, which are shared by everything). Keep discriminating values such as the destination host, because they distinguish real causes.
• MinHash (a compact fingerprint of a set of items). Represent each failure as a set of word pairs (shingles). Jaccard similarity is shared items divided by total distinct items. Tiny example: failure A has shingles {a, b, c, d} and failure B has {a, b, c, e}. They share 3 of 5 distinct items, so Jaccard = 3/5 = 0.6. One MinHash value is the item with the lowest hash score, where the hash function scores each item. If a hash function happens to rank e first (order e, a, b, d, c), A's minimum is a and B's minimum is e, so they disagree. If it ranks a first, both minimums are a and they agree. Over many hash functions they agree about 60% of the time, so the fraction of matching positions in two fingerprints estimates the Jaccard similarity without comparing the full sets.
• LSH banding: cut the fingerprint (a list of 24 MinHash values) into bands (consecutive slices); two failures become a candidate pair if any band matches exactly. "Rows" means how many hash values sit in one band. With 24 hashes in 8 bands of 3 rows, the chance a pair with Jaccard similarity s becomes a candidate is 1 - (1 - s^3)^8:

Similar pairs almost always survive, dissimilar ones mostly do not. More bands or fewer rows per band raises recall and the candidate count.

• Verification with TF-IDF cosine (TF-IDF weights a word up when it is frequent in one failure and down when it appears in most failures, so common words like error matter little; cosine measures the angle between two weight vectors, 1.0 meaning identical direction).
• Scale and memory: process in shards (independent partitions of the data, here split by exception type or language, that can be handled separately), store only 24 integers per signature (about 100,000 x 24 = 2.4 million integers), and assign new signatures incrementally by looking up their buckets rather than reclustering everything.

Worked example (run it)

How the code maps to the four stages: normalize is stage 1 (its regexes replace timestamps, addresses, request ids, build numbers and other numbers with fixed tokens like <ts> and <n>, so two runs of the same bug produce the same word list). Exact-match on a hash is not coded separately because near-identical texts already collapse in stage 3. shingles, minhash and the band loop that fills buckets are stage 3 (two failures that share a bucket become a candidate pair). tfidf, cosine and the threshold check are stage 4's verification, and the find/parent code in cluster is union-find, which merges every verified pair into one group.

import hashlib
import math
import re
from collections import Counter, defaultdict
from itertools import combinations

RAW = [
    # (id, language, raw failure text)
    ("f1", "python", "ConnectionError: HTTPSConnectionPool(host='pay.internal', port=443): timed out after 30.1s at 2026-03-02T09:14:55Z req=9f8e7d6c5b4a"),
    ("f2", "python", "ConnectionError: HTTPSConnectionPool(host='pay.internal', port=443): timed out after 29.8s at 2026-03-03T14:02:11Z req=1a2b3c4d5e6f"),
    ("f3", "java",   "java.net.SocketTimeoutException: connect timed out to pay.internal:443 after 30000 ms at 0x7f3a9c12"),
    ("f4", "java",   "java.net.SocketTimeoutException: connect timed out to pay.internal:443 after 30000 ms at 0x7f11aa20"),
    ("f9", "python", "ConnectionError: HTTPSConnectionPool(host='pay.internal', port=443): read timed out after 30s (Caused by ReadTimeoutError) at 2026-03-04T10:00:01Z req=00ff11ee22dd"),
    ("f10", "python", "ConnectionError: HTTPSConnectionPool(host='auth.internal', port=443): timed out after 30.2s at 2026-03-04T11:20:00Z req=aa11bb22cc33"),
    ("f5", "js",     "AssertionError: expected 41.98 to equal 42.00 in cart total, item 3 of 9"),
    ("f6", "js",     "AssertionError: expected 17.50 to equal 17.51 in cart total, item 7 of 9"),
    ("f7", "python", "KeyError: 'discount_code' in apply_discount at /home/ci/build_8812/cart/discount.py:88"),
    ("f8", "python", "KeyError: 'discount_code' in apply_discount at /home/ci/build_9034/cart/discount.py:88"),
]

def normalize(text):
    """Replace volatile parts (times, ids, addresses, numbers, build paths) with stable tokens."""
    t = text.lower()
    t = re.sub(r"\d{4}-\d{2}-\d{2}t[\d:]+z", "<ts>", t)
    t = re.sub(r"0x[0-9a-f]+", "<addr>", t)
    t = re.sub(r"req=[0-9a-f]+", "req=<id>", t)
    t = re.sub(r"build_\d+", "build_<n>", t)
    t = re.sub(r"\d+(\.\d+)?", "<n>", t)
    return re.findall(r"[a-z_<>]+", t)

def shingles(tokens, k=2):
    return {" ".join(tokens[i:i + k]) for i in range(len(tokens) - k + 1)}

def h(value, seed):
    return int(hashlib.md5(f"{seed}:{value}".encode()).hexdigest()[:8], 16)

NUM_HASHES, BANDS = 24, 8            # 8 bands of 3 rows each
ROWS = NUM_HASHES // BANDS

def minhash(sh):
    return [min(h(s, seed) for s in sh) for seed in range(NUM_HASHES)]

docs = {i: shingles(normalize(text)) for i, _, text in RAW}
sigs = {i: minhash(sh) for i, sh in docs.items()}

# LSH: two items become a candidate pair if ANY band of their signatures matches exactly.
buckets = defaultdict(set)
for i, sig in sigs.items():
    for b in range(BANDS):
        buckets[(b, tuple(sig[b * ROWS:(b + 1) * ROWS]))].add(i)
candidates = set()
for members in buckets.values():
    candidates.update(combinations(sorted(members), 2))

# Verify candidates with TF-IDF cosine similarity on the normalized tokens.
tokens = {i: normalize(text) for i, _, text in RAW}
df = Counter(w for toks in tokens.values() for w in set(toks))
N = len(tokens)
def tfidf(toks):
    tf = Counter(toks)
    return {w: c * math.log((1 + N) / (1 + df[w])) for w, c in tf.items()}
def cosine(a, b):
    dot = sum(a[w] * b.get(w, 0) for w in a)
    na = math.sqrt(sum(v * v for v in a.values()))
    nb = math.sqrt(sum(v * v for v in b.values()))
    return dot / (na * nb)
vec = {i: tfidf(t) for i, t in tokens.items()}

host_of = {}
for i, _, text in RAW:
    m = re.search(r"([a-z]+\.internal)", text)
    host_of[i] = m.group(1) if m else None

def cluster(threshold, require_same_host):
    parent = {i: i for i in tokens}
    def find(x):
        while parent[x] != x:
            parent[x] = parent[parent[x]]
            x = parent[x]
        return x
    for a, b in sorted(candidates):
        if require_same_host and host_of[a] != host_of[b]:
            continue
        if cosine(vec[a], vec[b]) >= threshold:
            parent[find(a)] = find(b)
    groups = defaultdict(list)
    for i in tokens:
        groups[find(i)].append(i)
    return sorted(groups.values())

print("all pairs:", N * (N - 1) // 2, "candidate pairs:", len(candidates))
for a, b in sorted(candidates):
    print(f"cosine {a}-{b} = {cosine(vec[a], vec[b]):.2f}")

print("\nA) threshold 0.6, text only:")
for g in cluster(0.6, False):
    print("  ", g)
print("B) threshold 0.5, merge only within the same extracted host:")
for g in cluster(0.5, True):
    print("  ", g)

# Second tier: link clusters across languages by a shared extracted feature (destination host).
by_host = defaultdict(list)
for i, host in host_of.items():
    if host:
        by_host[host].append(i)
print("C) cross-language view, grouped by host:", dict(by_host))

Output:

all pairs: 45 candidate pairs: 9
cosine f1-f10 = 0.79
cosine f1-f2 = 1.00
cosine f1-f9 = 0.59
cosine f10-f2 = 0.79
cosine f10-f9 = 0.47
cosine f2-f9 = 0.59
cosine f3-f4 = 1.00
cosine f5-f6 = 1.00
cosine f7-f8 = 1.00

A) threshold 0.6, text only:
   ['f1', 'f2', 'f10']
   ['f3', 'f4']
   ['f5', 'f6']
   ['f7', 'f8']
   ['f9']
B) threshold 0.5, merge only within the same extracted host:
   ['f1', 'f2', 'f9']
   ['f10']
   ['f3', 'f4']
   ['f5', 'f6']
   ['f7', 'f8']
C) cross-language view, grouped by host: {'pay.internal': ['f1', 'f2', 'f3', 'f4', 'f9'], 'auth.internal': ['f10']}

Read it as a precision versus recall story. At threshold 0.6 on text alone, f10 (host auth.internal) is wrongly merged with the pay.internal failures because the two texts differ by one token (0.79 cosine): a precision error (grouping unrelated failures, which sends a bug to the wrong owner). Meanwhile f9 is a rewording of the same payment timeout but scores only 0.59 and is left alone: a recall error (splitting one cause into two groups, which duplicates bug reports). Variant B uses the discriminating host as a guard and a looser 0.5 threshold, and gets both right. Cross-language links (the Python and Java payment timeouts, f1 to f4) cannot come from text similarity, so a second-tier group by shared extracted features (view C) ties them together for display.

Coping with format drift

• Version the normalizer and store raw text, normalized text and normalizer_version per signature. When a log format changes (a new timestamp style, a renamed exception wrapper) you re-derive, instead of losing history.
• Stable group ids: keep a mapping from the old group id to the new when groups merge or split, so bug tickets stay linked.
• Drift monitor: alert when the fraction of new signatures that match no existing group jumps, or when the exact-match hit rate drops. Both point to a format change.
• Regression tests for the parser: keep a corpus of real failures per language with expected groups, and run it on every normalizer change.

Precision versus recall, and the recommendation

Use two thresholds. A strict one drives automatic actions (filing bugs, assigning owners, suppressing duplicates), where a wrong merge hides a real problem. A looser one drives the display ("possibly related"), where a miss costs only a human click. Tune both on a few hundred human-labelled pairs, measure precision as correct merges divided by all merges, and recall as found true links divided by all true links, and re-check monthly. If precision matters more for your consumers (auto-filed tickets), pick precision first; if humans triage, favour recall.

Pitfalls: over-normalizing (removing numbers that distinguish item 3 from item 300 when they matter), letting library frames dominate similarity, chaining (union-find can merge two groups through one weak bridge pair, like A similar to B and B similar to C making A and C one group even though A and C are dissimilar; cap the group diameter, meaning the largest allowed distance between any two members, or require the average similarity to hold), and reclustering from scratch nightly, which reshuffles ids.

Direct answer

Treat it as a small data pipeline: collect the raw report at the end of every CI run (CI is continuous integration, the automated system that builds and tests each change), parse it into one row per test attempt, enrich it with run metadata, load it idempotently (so that loading the same data twice has the same effect as loading it once) into a store, and serve dashboards from pre-aggregated summaries (totals such as daily pass counts, computed once and stored, rather than recomputed from raw rows on every page view). The first thing that breaks as volume grows is dashboard reads over the raw table, and my design puts summaries in front of it from day one.

Pipeline stages

1. Emit: test runners write JUnit-style XML or JSON (a standard result format) into the job workspace.
2. Collect: a shared, reusable pipeline step that always runs, even when tests fail, and uploads the report.
3. Parse and normalize: convert each report to uniform records. This includes turning each failure message into a failure signature: a short fingerprint made by stripping run-specific parts (timestamps, ids, memory addresses) from the error text, so the same bug always produces the same signature.
4. Enrich: add commit, branch, pull request, runner, environment and pipeline id.
5. Ingest: send records through a queue (a buffer that holds incoming records so a burst of uploads does not overwhelm the database), then batch-insert with an idempotency key (a unique identifier per record, here run id plus test id plus attempt, so a repeated upload is recognised and ignored).
6. Store: a hot store (fast, indexed storage for recent rows), a summaries table (rollups: pre-computed totals per day, suite or test), and cold storage (cheap, slower storage for old data).
7. Serve: dashboards and alerts read summaries; drill-down reads the hot store.

What to record per result

A stable test id (suite, class and name, so history survives renames of files), run id, attempt number (to see retries), status (passed, failed, error, skipped), duration, commit, branch, runner, a normalized failure signature, a link to the full log in object storage (cheap file-style storage for large blobs such as logs and screenshots), and a finish timestamp. This is the schema, run for real:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE test_result (
  run_id      TEXT NOT NULL,   -- CI run
  test_id     TEXT NOT NULL,   -- stable id: suite + class + name
  attempt     INTEGER NOT NULL DEFAULT 1,
  status      TEXT NOT NULL CHECK (status IN ('passed','failed','error','skipped')),
  duration_ms INTEGER NOT NULL,
  commit_sha  TEXT NOT NULL,
  branch      TEXT NOT NULL,
  runner      TEXT NOT NULL,
  signature   TEXT,            -- normalized failure signature, NULL when passed
  finished_at TEXT NOT NULL,
  PRIMARY KEY (run_id, test_id, attempt)   -- makes re-uploads idempotent
);
CREATE INDEX idx_result_test_time ON test_result (test_id, finished_at);
""")

rows = [
    ("run1", "api.Login.ok",  1, "passed", 120, "abc", "main", "r1", None,         "2026-09-01T02:00"),
    ("run1", "api.Pay.refund",1, "failed", 900, "abc", "main", "r1", "Timeout|db", "2026-09-01T02:00"),
    ("run1", "api.Pay.refund",2, "passed", 850, "abc", "main", "r1", None,         "2026-09-01T02:01"),
    ("run2", "api.Login.ok",  1, "passed", 130, "def", "main", "r2", None,         "2026-09-02T02:00"),
    ("run2", "api.Pay.refund",1, "passed", 880, "def", "main", "r2", None,         "2026-09-02T02:00"),
]
for _ in range(2):  # upload the same batch twice: the second pass must be a no-op
    db.executemany("INSERT OR IGNORE INTO test_result VALUES (?,?,?,?,?,?,?,?,?,?)", rows)

print("rows stored:", db.execute("SELECT COUNT(*) FROM test_result").fetchone()[0])

# A test is "flaky in a run" when a failed attempt is followed by a passing attempt of the same test.
flaky = db.execute("""
SELECT test_id, COUNT(DISTINCT run_id) AS flaky_runs
FROM test_result f
WHERE status IN ('failed','error')   -- a skipped attempt is not a failure
  AND EXISTS (SELECT 1 FROM test_result p
              WHERE p.run_id = f.run_id AND p.test_id = f.test_id
                AND p.attempt > f.attempt AND p.status = 'passed')
GROUP BY test_id""").fetchall()
print("flaky:", flaky)

Output:

rows stored: 5
flaky: [('api.Pay.refund', 1)]

The batch was uploaded twice yet 5 rows are stored, so retried uploads cannot double count. Plain-language walkthrough of the flaky query: take every row f that did not pass (status failed or error). Keep it only if the same run has another row p for the same test with a later attempt number that passed. Count the distinct runs where that happened, per test. Here api.Pay.refund failed on attempt 1 and passed on attempt 2 in run1, so it counts once; in run2 it simply passed. Nothing else failed, so nothing else appears. The store here is SQLite for demonstration; a production system would use a server database or warehouse with the same shape.

Latency versus history (two access patterns)

• Near-real-time summary: at the end of each run, write one small summary row (counts, duration, top signatures) so the run page shows within seconds to minutes.
• 90-day history and signature search: the indexed hot store, partitioned by date (split into one chunk per day so queries and cleanups touch only the days they need).
• Two years of data: keep daily rollups per test and suite (pass rate, flake count, 95th-percentile duration, the time that 95% of runs come in under) indefinitely, move raw rows past 90 days to compressed files in object storage (a warehouse, meaning a database built for large analytical queries, is the other common home), and query those only for investigations.

What breaks first as volume grows

1. Dashboard queries scanning the raw table (fix: rollups, indexes, partitions).
2. Write spikes when many pipelines finish at once (fix: queue and batch inserts).
3. Storage bloat from stack traces and logs kept inline (fix: store a truncated signature, put full logs in object storage).
4. Unstable test ids, which quietly break history, and unnormalized error text, which explodes distinct signatures.

Hooking collection into every pipeline, safely

• Ship it as a shared template step so teams opt out rarely, and make it best effort: a reporting failure must never fail the build, but should alert, and "runs with no report" should itself be a tracked metric.
• Security: give each pipeline a write-only token (it can upload results but not read or delete anything) with narrow scope, kept in a secret store (a service that holds credentials outside the code), and do not expose it to pull requests from forks (copies of the repository made by outside contributors, whose code you do not trust), scrub secrets and personal data from logs before upload, validate and size-limit uploads, and enforce access control on dashboards.
• Performance: upload asynchronously, compress, and cap payload size so collection adds little to the run time.

Direct answer

Attribute each defect to the release that introduced it, count how many of those were found in production, and divide by all defects for that release. Use a LEFT JOIN from releases so a release with no defects still shows, and guard the division. The number is misleading for a recent release mainly because production defects surface over weeks (discovery lag), so a young release looks better than it is.

Structured elaboration

Approach. One row per release (LEFT JOIN from releases), grouped on the release. Escape count = defects where found_in_env = 'production'. Total = all defects with introduced_in_release equal to that release, wherever found. Percent = escapes divided by total times 100, rounded to two decimals. Only standard constructs are used (LEFT JOIN, CASE, SUM, COUNT, GROUP BY). ROUND(x, 2) exists in every major engine, though strict standard SQL would write CAST(... AS DECIMAL(5,2)).

Reading the query, clause by clause (core)

• LEFT JOIN defects d ON d.introduced_in_release = r.id: start from every release and attach its defects. Unlike a plain join, a release with zero defects (release 5) still gets a row.
• CASE WHEN d.found_in_env = 'production' THEN 1 ELSE 0 END inside SUM: turns each defect into 1 if it escaped and 0 if not, so the sum is the escape count. Release 1's four defects give 0 + 1 + 0 + 0 = 1.
• COUNT(d.id): counts only defects that exist. For release 5 there are none, so it is 0 (COUNT(*) would wrongly count the empty join row as 1).
• COALESCE(x, 0): replaces NULL with 0. SUM over a release with no defects returns NULL, and the escape count should read 0.
• 100.0 * ... / COUNT(...): the 100.0 (a decimal, not the integer 100) forces decimal division; with integers, 1 / 4 would truncate to 0 in some engines. ROUND(..., 2) gives two decimals.
• CASE WHEN COUNT(d.id) = 0 THEN NULL: dividing by zero is an error, so a release with no defects shows a blank rate instead.

Key points

• Release 1: 1 escape out of 4 defects = 25 percent. Release 3: 1 of 3 = 33.33 percent.
• Release 5 has no defects, so its rate is NULL (unknown), not 0. The CASE WHEN COUNT(d.id) = 0 guard prevents a divide-by-zero.
• Optional extra (not asked for): detection rate (the sibling metric: share of a release's defects found before production) equals 100 minus the escape rate here (75.0 for release 1) because "found before production" is the exact complement. They diverge if you count only defects found by QA, or if unattributed defects are handled differently in the two queries.

Complexity (rarely asked; a brief mention is enough). One pass over the join: O(R + D) with a hash join, where R is releases and D is defects. An index on defects(introduced_in_release) keeps the join cheap; memory is one group per release.

Edge cases

• Inconsistent environment labels ("prod" versus "production") silently undercount escapes. Normalise the values.
• Defects with a NULL introduced_in_release drop out of every group. Count them separately.
• A defect found in production but introduced in an older release counts against the older release, which is right but means past rates get restated.

Worked example

Save as defect_escape.sql and run sqlite3 -header -column :memory: < defect_escape.sql. The first query is the answer. The second is an optional extra, detection rate, and it uses two things you can skip in an interview: the FILTER clause (a shorthand for counting only rows that match a condition; PostgreSQL syntax, also accepted by SQLite 3.30 and later) and NULLIF(COUNT(d.id), 0), which turns a zero denominator into NULL so the division returns NULL instead of an error.

CREATE TABLE releases (id INT, name TEXT, release_date TIMESTAMP);
CREATE TABLE defects (id INT, title TEXT, found_in_env TEXT, introduced_in_release INT, found_at TIMESTAMP);
INSERT INTO releases VALUES
 (1,'2.1','2026-01-10 09:00:00'),
 (2,'2.2','2026-02-14 09:00:00'),
 (3,'2.3','2026-03-21 09:00:00'),
 (4,'2.4','2026-04-25 09:00:00'),
 (5,'2.5','2026-05-30 09:00:00');
INSERT INTO defects VALUES
 (1,'Login timeout','qa',1,'2026-01-05 10:00:00'),
 (2,'Wrong tax rounding','production',1,'2026-01-20 14:00:00'),
 (3,'Export header typo','staging',1,'2026-01-08 11:00:00'),
 (4,'Search crash','qa',1,'2026-01-06 15:00:00'),
 (5,'Cart total drift','production',2,'2026-02-25 08:30:00'),
 (6,'Profile save fails','qa',2,'2026-02-10 09:15:00'),
 (7,'Slow report page','production',2,'2026-03-02 16:45:00'),
 (8,'Email link broken','qa',2,'2026-02-11 13:00:00'),
 (9,'Coupon stacking','qa',3,'2026-03-15 10:20:00'),
 (10,'Refund rounding','production',3,'2026-03-28 12:00:00'),
 (11,'Avatar upload','staging',3,'2026-03-16 17:00:00'),
 (12,'Sort order flips','qa',4,'2026-04-20 11:00:00'),
 (13,'Tooltip overlap','qa',4,'2026-04-21 09:45:00'),
 (14,'Password reset loop','staging',4,'2026-04-22 15:30:00');

SELECT r.id AS release_id,
       r.name,
       COALESCE(SUM(CASE WHEN d.found_in_env = 'production' THEN 1 ELSE 0 END), 0) AS escape_count,
       COUNT(d.id) AS total_count,
       CASE WHEN COUNT(d.id) = 0 THEN NULL
            ELSE ROUND(100.0 * SUM(CASE WHEN d.found_in_env = 'production' THEN 1 ELSE 0 END) / COUNT(d.id), 2)
       END AS escape_pct
FROM releases r
LEFT JOIN defects d ON d.introduced_in_release = r.id
GROUP BY r.id, r.name
ORDER BY r.id;

SELECT r.id AS release_id,
       COUNT(*) FILTER (WHERE d.found_in_env <> 'production') AS found_before_prod,
       COUNT(d.id) AS total_count,
       ROUND(100.0 * COUNT(*) FILTER (WHERE d.found_in_env <> 'production') / NULLIF(COUNT(d.id), 0), 2) AS detection_pct
FROM releases r
LEFT JOIN defects d ON d.introduced_in_release = r.id
GROUP BY r.id
ORDER BY r.id;

Output (SQLite prints 25.0 where PostgreSQL prints 25.00):

release_id  name  escape_count  total_count  escape_pct
----------  ----  ------------  -----------  ----------
1           2.1   1             4            25.0
2           2.2   2             4            50.0
3           2.3   1             3            33.33
4           2.4   0             3            0.0
5           2.5   0             0
release_id  found_before_prod  total_count  detection_pct
----------  -----------------  -----------  -------------
1           3                  4            75.0
2           2                  4            50.0
3           2                  3            66.67
4           3                  3            100.0
5           0                  0

Trade-offs and pitfalls

What makes this misleading for a recent release

• Discovery lag (technically called right-censoring: the outcomes for a young release are still arriving, like judging a race before everyone has finished): production defects appear over weeks. Release 4 shows 0 escapes and 100 percent detection only because it shipped recently, not because it is the best release. Compare releases at equal age, for example only defects found within 30 days of the release date (in standard SQL, d.found_at < r.release_date + INTERVAL '30' DAY; the interval syntax varies by engine), and mark younger releases as immature.
• Tiny denominators: with 3 defects, one escape moves the rate by 33 points.
• Severity mix: a cosmetic escape and a data-loss escape count the same. Report critical escapes separately.
• Restatement: a later production find attributed to an old release changes that release's number.