This is an old revision of the document!
Community Health
Time between a new issue is opened and a maintainer responds Also called: bug response rate. The maintainer is believed to not “pile on” but try to solve an issue.
Below queries are using users with commit rights, not maintainer.
GHTorrent Database
SELECT avg(time_to_member_comment_in_days) as avg_days_to_member_comment, project_name, url FROM ( SELECT DATEDIFF(earliest_member_comment, issue_created) time_to_member_comment_in_days, project_id, issue_id, project_name, url FROM (SELECT projects.id as project_id, MIN(issue_comments.created_at) as earliest_member_comment, issues.created_at as issue_created, issues.id as issue_id, projects.name as project_name, url FROM msr14.repo_labels join projects on repo_labels.repo_id = projects.id join issue_labels on issue_labels.label_id = repo_labels.id join project_members on projects.id = project_members.repo_id join issues on issue_labels.issue_id = issues.id join issue_comments on issue_comments.issue_id = issues.id where repo_labels.name = 'bug' and issue_comments.user_id = project_members.user_id group by issues.id) as earliest_member_comments) as time_to_member_comment group by project_id
SELECT avg(time_to_member_comment_in_days) as avg_days_to_member_comment, project_name, url FROM ( SELECT DATEDIFF(earliest_member_comment, issue_created) time_to_member_comment_in_days, project_id, issue_id, project_name, url FROM (SELECT projects.id as project_id, MIN(issue_comments.created_at) as earliest_member_comment, issues.created_at as issue_created, issues.id as issue_id, projects.name as project_name, url FROM projects join project_members on projects.id = project_members.repo_id join issues on issues.repo_id = projects.id join issue_comments on issue_comments.issue_id = issues.id where issue_comments.user_id = project_members.user_id group by issues.id) as earliest_member_comments) as time_to_member_comment group by project_id
SELECT issues.id AS "issue_id", issues.created_at AS "created_at", MIN(issue_comments.created_at) AS "responded_to" FROM issues JOIN issue_comments ON issue_comments.issue_id = issues.id WHERE issue_comments.user_id IN (SELECT users.id FROM users JOIN commits WHERE commits.author_id = users.id AND commits.project_id = 78852) AND issues.repo_id = 78852 GROUP BY issues.id
Anna Buhman