Skip to main content

Finding Stale Issues and PRs โŒ›

Patrick DeVivo

Unanswered issues and pull requests on GitHub repos are no fun, especially if you're the one asking for help ๐Ÿ™‚. I've seen many projects making use of automation to close (or comment on) them, though folks have argued against that.

Regardless, identifying unanswered issues can be valuable, especially for maintainers to ensure they're addressing user concerns - or even for anyone looking for places to help.

You can use the MergeStat GitHub tables to identify stale issues or PRs in a repo (or even across many repos).

info

MergeStat is an open-source tool for running SQL queries against git repositories and related data sources (like the GitHub API). Check us out if you're not familiar.

Stale Issuesโ€‹

This will return the oldest 25 issues created more than 30 days ago, with no comments, that remain open.

SELECT
title, author_login, comment_count, created_at, url
FROM github_repo_prs('uber-go/zap') -- replace with your repo
WHERE
created_at < date('now', '-30 days') -- replace with how long you care about (https://www.sqlite.org/lang_datefunc.html)
AND (merged = 0 OR closed = 0)
AND comment_count = 0
ORDER BY created_at ASC
LIMIT 25

(see example output)

Stale PRsโ€‹

SELECT
title, author_login, comment_count, created_at, url
FROM github_repo_prs('mergestat/mergestat') -- replace with your repo
WHERE
created_at < date('now', '-30 days') -- replace with how long you care about (https://www.sqlite.org/lang_datefunc.html)
AND merged = 0
AND closed = 0
AND comment_count = 0
ORDER BY created_at ASC
LIMIT 25
note

You can run these queries either in the Public workspace or by installing the CLI. In either case you will need to supply a GitHub token for API authentication.

export GITHUB_TOKEN="my-github-token"
cat query.sql | mergestat -v

Considerationsโ€‹

  1. Maybe comment_count = 0 isn't good enough to indicate "staleness" - maybe finding issues/PRs where there are only comments from the original author, or where there are no comments from maintainers is more accurate.
  2. The above queries can be joined with github_org_repos to see stale PRs and issues across an entire org, not just in a single repo.
  3. Maybe you could produce a dashboard (charts) to track issue staleness and alert on it - an SLA on issue response time?

If you're interested in exploring these use cases, feel free to come say hi on our Slack or shoot us a note on Twitter.