User Tools

Site Tools


oss-health-metrics:metrics:reopened-issues

This is an old revision of the document!


Reopened Issues

1. Acronym

2. Alternate Names

3. Classification

Reopened Issues

4. Description

Rate of issues closed but discussion continues or issues that were closed and re-opened

Pseudo Code

Visualization

Interpretation

5. Example Implementation

GHTorrent: Reopened issues

Rate of issues closed but discussion continues or issues that were closed and re-opened

Total closed issues by project

select count(distinct issues.id) as total_issues, projects.name as project_name, projects.url as project_url 
from    
issues join projects
on issues.repo_id = projects.id
join issue_events
on issue_events.issue_id = issues.id
where issue_events.action = 'closed'
group by projects.id

Reopened issues:

  select count(distinct issues.id) as total_reopened_issues, projects.name as project_name
  from    
  issues join projects
  on issues.repo_id = projects.id
  join issue_events
  on issue_events.issue_id = issues.id
  where issue_events.action = 'reopened'
  group by projects.id

issues with comments after issue is closed:

  select count(distinct comment_issue_id) as num_issues_with_comments_after_closed, comment_project_name as project_name
  from
  (select issues.id as comment_issue_id, projects.id as comment_project_id, issue_comments.created_at as comment_date, projects.name as comment_project_name
  from issue_comments
  join issues on issue_comments.issue_id = issues.id
  join projects on projects.id = issues.repo_id) as comment_issues
  join
  (select issues.id as closed_issue_id, projects.id as closed_project_id, issue_events.created_at as closed_date
  from    
  issues join projects
  on issues.repo_id = projects.id
  join issue_events
  on issue_events.issue_id = issues.id
  where issue_events.action = 'closed') as closed_issues
  on closed_issue_id = comment_issue_id AND comment_project_id = closed_project_id AND comment_date > closed_date
  group by comment_project_id

Issues with no comments after issue closed:

  select count(distinct issues.id) as num_issues_no_comments_after_close, projects.name as project_name
  from    
  issues join projects
  on issues.repo_id = projects.id
  join issue_events
  on issue_events.issue_id = issues.id
  where issue_events.action = 'closed' AND (issues.id, projects.id)
    not in(
      select comment_issue_id, comment_project_id
    from
    (select issues.id as comment_issue_id, projects.id as comment_project_id, issue_comments.created_at as comment_date, projects.name as comment_project_name
    from issue_comments
    join issues on issue_comments.issue_id = issues.id
    join projects on projects.id = issues.repo_id) as comment_issues
    join
    (select issues.id as closed_issue_id, projects.id as closed_project_id, issue_events.created_at as closed_date
    from    
    issues join projects
    on issues.repo_id = projects.id
    join issue_events
    on issue_events.issue_id = issues.id
    where issue_events.action = 'closed') as closed_issues
    on closed_issue_id = comment_issue_id AND comment_project_id = closed_project_id AND comment_date > closed_date)
  group by projects.id

6. Data Exchange Format

7. Known Implementations

8. References to Academic Literature

9. Internet References

10. Contributors

Anna Buhman

oss-health-metrics/metrics/reopened-issues.1493915709.txt.gz · Last modified: 2017/05/04 16:35 by abuhman