0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for linguists, teachers and students of the Ukrainian language

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

SELECT
  [UserId], [No]
FROM
  (
  SELECT
    [u].[Id] [UserId],
    COUNT(1) [No]
  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]
  WHERE
    [u].[Reputation] >= 3000 AND
    [ph].[PostHistoryTypeId] = 10 AND -- 10 = "Post Closed"
    [p].[ClosedDate] >= @startDate
  GROUP BY
    [u].[Id], [u].[DisplayName]
  ) [cv]
ORDER BY
[cv].[No] DESC

view current version

edited jan 4 13 jon.doe10212

created jan 4 13 anonymous