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