npm ci                       # first time only, from the repo root
GITHUB_TOKEN=$(gh auth token) node utils/test-results-db/cli.ts update --lookback-days 3

node --input-type=module -e '
import { DuckDBInstance } from "@duckdb/node-api";
const conn = await (await DuckDBInstance.create("utils/test-results-db/test-results.duckdb")).connect();
console.table((await conn.runAndReadAll(process.argv[1])).getRowObjectsJson());
' "SELECT count(*) FROM test_results"

WITH per_run AS (
  SELECT project_name, file, test_title, run_id, run_attempt,
         arg_max(status, retry) AS final_status,
         any_value(expected_status) AS expected
  FROM test_results
  GROUP BY project_name, file, test_title, run_id, run_attempt)
SELECT project_name, test_title,
       count(*) AS runs,
       count(*) FILTER (WHERE final_status IN ('failed','timedOut')) AS failed_runs,
       count(*) FILTER (WHERE final_status = 'passed') AS passed_runs,
       round(100.0 * count(*) FILTER (WHERE final_status IN ('failed','timedOut'))
             / count(*), 1) AS fail_pct
FROM per_run
WHERE expected = 'passed'
GROUP BY project_name, test_title
HAVING failed_runs > 0 AND passed_runs > 0 AND runs >= 10
ORDER BY least(failed_runs, passed_runs) DESC, failed_runs DESC
LIMIT 20;

SELECT project_name, test_title, count(*) AS runs
FROM test_results
WHERE list_contains(tags, '@slow')
GROUP BY project_name, test_title
ORDER BY runs DESC
LIMIT 20;

node --input-type=module <<'EOF'
import { DuckDBInstance } from "@duckdb/node-api";

const repository = "microsoft/playwright";
const test = {
  projectName: "firefox-library",
  file: "library/proxy.spec.ts",
  testTitle: "should exclude patterns",
  botName: "firefox-macos-15-large",
};

const conn = await (await DuckDBInstance.create(
  "utils/test-results-db/test-results.duckdb"
)).connect();
const result = await conn.runAndReadAll(`
  WITH per_run AS (
    SELECT run_id, run_attempt,
           any_value(run_started_at) AS run_started_at,
           arg_max(status, retry) AS final_status,
           arg_max(expected_status, retry) AS expected_status,
           list(status ORDER BY retry) AS attempt_statuses
    FROM test_results
    WHERE project_name = $projectName
      AND file = $file
      AND test_title = $testTitle
      AND bot_name = $botName
    GROUP BY run_id, run_attempt
  )
  SELECT run_id, run_attempt, final_status, attempt_statuses
  FROM per_run
  WHERE expected_status = 'passed'
    AND final_status IN ('passed', 'failed', 'timedOut')
  ORDER BY run_started_at, run_id, run_attempt
`, test);

const markdown = result.getRowObjectsJson().map(row => {
  const rescued = row.final_status === "passed" &&
    row.attempt_statuses.some(status => status === "failed" || status === "timedOut");
  const emoji = rescued ? "🟧" : row.final_status === "passed" ? "🟩" : "🟥";
  const url = `https://github.com/${repository}/actions/runs/${row.run_id}/attempts/${row.run_attempt}`;
  return `[${emoji}](${url})`;
}).join("");

console.log(markdown);
EOF

[🟩](https://github.com/microsoft/playwright/actions/runs/123/attempts/1)[🟧](https://github.com/microsoft/playwright/actions/runs/456/attempts/1)[🟥](https://github.com/microsoft/playwright/actions/runs/789/attempts/1)

# List the run's blob artifacts and find the one for this bot_name:
gh api /repos/microsoft/playwright/actions/runs/<run_id>/artifacts \
  --jq '.artifacts[] | select(.name | startswith("blob-report")) | {id, name}'

# Download it (name == "blob-report-<bot_name>"):
gh api /repos/microsoft/playwright/actions/artifacts/<artifact_id>/zip > blob.zip