Reopened Issues

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

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

Anna Buhman