Close and reopen votes


Please login or register to vote for this query.

(click on this box to dismiss)


Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

-- Returns all close voters since StartDate, ordered from most to least votes
-- Only counts closed votes that actually resulted in a closure,
-- and does not count deleted questions

-- I wouldn't recommend running this on StackOverflow due to its size

-- Variables - Set start date to timeframe you want
declare @startDate datetime
set @startDate = '9/1/2017'

declare @userid int
declare @users table
  userid int,
  closedcount int,
  reopencount int

-- Cursor for all users who can vote to close
declare cur cursor for 
    select id 
    from users
    where reputation >= 3000
      and lastaccessdate >= @startDate
open cur
fetch next from cur into @userid
while @@fetch_status = 0

  insert into @users(userid, closedcount, reopencount)
  SELECT @userid, 
    sum(case when PostHistoryTypeId = 10 then 1 else 0 end),
    sum(case when PostHistoryTypeId = 11 then 1 else 0 end)
  FROM PostHistory 
  WHERE PostHistoryTypeId in (10, 11) 
  AND Text LIKE '%"Id":' + CAST(@userid AS nvarchar) + ',%'
  and creationdate >= @startDate
  fetch next from cur into @userid

close cur
deallocate cur

-- Return results

select id as [User Link], * from

(select id,reputation
from users
select owneruserid from posts
where creationdate > ##startdate:string?2017-01-01##
group by owneruserid

) as recentUsers
on = recentusers.owneruserid
where reputation > 3000) as activeUsers

left outer join

(select u.closedcount, u.reopencount, u.userid
from @users as u
left outer join users on u.userid =
where closedcount > 0 or reopencount > 0) as closeStats

on closestats.userid =

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results