A breakdown by reputation grouping on the percent of questions that have been reopened. Shows all questions that were reopened and questions that are closed at the moment.
Q&A for professional and enthusiast programmers
declare @ranges Table ( MinRep int not null, MaxRep int not null ) insert into @ranges (MinRep, MaxRep) values (1, 10), (10, 20), (20, 50), (50, 100), (100, 300), (300, 500), (500, 1000), (1000, 10000); ;with TotalClosedCounts as ( select r.MinRep, r.MaxRep, count(r.MinRep) [totalClosedCount] from @ranges r inner join Users u on u.Reputation between r.MinRep and r.MaxRep inner join Posts p on p.OwnerUserId = u.Id and p.ClosedDate is not null group by r.MinRep, r.MaxRep ), ReopenedCounts as ( select r.MinRep, r.MaxRep, count(r.MinRep) [reopenedCount] from @ranges r inner join Users u on u.Reputation between r.MinRep and r.MaxRep inner join Posts p on p.OwnerUserId = u.Id inner join PostHistory closeHistory on closeHistory.PostId = p.Id and closeHistory.PostHistoryTypeId = 10 --post closed inner join PostHistory reopenHistory on reopenHistory.PostId = p.Id and reopenHistory.PostHistoryTypeId = 11 --post reopened and reopenHistory.CreationDate > closeHistory.CreationDate group by r.MinRep, r.MaxRep ) select r.MinRep, r.MaxRep, rc.reopenedCount, tc.totalClosedCount, (rc.reopenedCount * 1.0 / tc.totalClosedCount * 100) [reopen %] from @ranges r inner join TotalClosedCounts tc on tc.MinRep = r.MinRep and tc.MaxRep = r.MaxRep inner join ReopenedCounts rc on rc.MinRep = r.MinRep and rc.MaxRep = r.MaxRep order by r.MinRep asc