0

Please login or register to vote for this query.

(click on this box to dismiss)

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 enthusiasts and power users of the Android operating system

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

view current version

edited may 22 14

created may 22 14 gunr2171