Top Close Voters

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

-- 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 TOP 10
  [u].[DisplayName],
  COUNT(1)
FROM [Users] [u] (NOLOCK)
  INNER JOIN [PostHistory] [ph] (NOLOCK) ON [ph].[Text] LIKE '%"Id":' + CAST([u].[Id] AS NVARCHAR) + ',%'
  INNER JOIN [Posts] [p] (NOLOCK) ON [p].[Id] = [ph].[PostId]
--  INNER JOIN [PostHistory] [ph] (NOLOCK) ON [ph].[PostId] = [p].[Id]
WHERE
  [u].[Reputation] >= 3000 AND
  [ph].[PostHistoryTypeId] = 10 AND
  [p].[ClosedDate] >= @startDate
GROUP BY
  [u].[Id], [u].[DisplayName]
ORDER BY
  COUNT(1) DESC
--  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

select * from PostHistoryTypes

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results