3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

-- 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/2015'

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
      and lastaccessdate >= @startDate
    
open cur
fetch next from cur into @userid
while @@fetch_status = 0
begin

  insert into @users(userid, closedcount)
  SELECT @userid, count(*)
  FROM PostHistory 
  WHERE PostHistoryTypeId = 10 
  AND Text LIKE '%"Id":' + CAST(@userid AS nvarchar) + ',%'
  and creationdate >= @startDate
  
  fetch next from cur into @userid
end

close cur
deallocate cur

-- Add Mark Trapp
insert into @users(userid, closedcount)
SELECT 8, count(*)
FROM PostHistory 
WHERE PostHistoryTypeId = 10 
AND Text LIKE '%"Id":' + CAST(8 AS nvarchar) + ',%'
and creationdate >= @startDate

-- Add MichaelT
insert into @users(userid, closedcount)
SELECT 40980, count(*)
FROM PostHistory 
WHERE PostHistoryTypeId = 10 
AND Text LIKE '%"Id":' + CAST(40980 AS nvarchar) + ',%'
and creationdate >= @startDate

-- Return results
select top 100 u.ClosedCount, 
case when u.userid = 8 then 'Mark Trapp' 
     when u.userid = 40980 then 'MichaelT'
     else users.displayname end as 'User', 
convert(nvarchar, (select max(creationdate) from posts where ownerUserId = users.id), 101) as [LastPostDate],
convert(nvarchar, (select max(creationdate) from votes where userId = users.id), 101) as [LastVoteDate]

from @users as u
left outer join users on u.userid = users.id
order by u.closedcount desc

fork query

edited mar 17 16 Rachel

created jan 4 13 anonymous