Top Close Voters

Q&A for expert programmers interested in professional discussions on software development

-- 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 = '1/1/2012'
declare @userid int
declare @users table
(
userid int,
closedcount int
)
-- Cursor for all users who can vote to close
declare cur cursor for
select id
from users
where reputation >= 3000
open cur
fetch next from cur into @userid
while @@fetch_status = 0
begin
insert into @users(userid, closedcount)
SELECT @userid, count(*)
FROM PostHistory
inner join posts on posthistory.postid = posts.id
WHERE PostHistoryTypeId = 10
AND Text LIKE '%"Id":' + CAST(@userid AS nvarchar) + ',%'
and posts.closeddate >= @startDate
fetch next from cur into @userid
end
close cur
deallocate cur
-- Return results
select top 100 u.*, users.displayname
from @users as u
inner join users on u.userid = users.id
order by u.closedcount desc