Top Close Voters

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

-- 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,
  @totalCloseVoteCount FLOAT
  
SET @startDate = '1/1/2012'

SELECT
  @totalCloseVoteCount = SUM([cv].[No])
FROM
  (
  SELECT
    COUNT(1) [No]
  FROM
    [PostHistory] [ph] (NOLOCK)
    INNER JOIN [Posts] [p] (NOLOCK) ON [p].[Id] = [ph].[PostId]
  WHERE
    [ph].[PostHistoryTypeId] = 10 AND -- 10 = "Post Closed"
    [p].[ClosedDate] >= @startDate
  ) [cv]

SELECT TOP 500
  [u].[DisplayName] [Name],
  ROUND(100.0 / (@totalCloseVoteCount / COUNT(1)), 1) [PercentOfClosedPostsInvolvedIn],
  COALESCE([c1].[No], 0) + COALESCE([se1].[No], 0) [CommentsAndEdits],
  [c1].[No] [Comments],
  [se1].[No] [Edits],
  [p1].[No] [Posts],
  COUNT(1) [TotalClosedPostsInvolvedIn],
  @totalCloseVoteCount [TotalClosedPosts]
FROM [Users] [u] (NOLOCK)
  LEFT JOIN [PostHistory] [ph] (NOLOCK) ON [ph].[Text] LIKE '%"Id":' + CAST([u].[Id] AS NVARCHAR) + ',%'
  LEFT JOIN [Posts] [p] (NOLOCK) ON [p].[Id] = [ph].[PostId]
  LEFT JOIN
  (
    SELECT [p].[OwnerUserId] [UserId], COUNT(1) [No]
    FROM [Posts] [p]
    WHERE [p].[CreationDate] >= @startDate
    GROUP BY [p].[OwnerUserId]
  ) [p1] ON [u].[Id] = [p1].[UserId]
  LEFT JOIN
  (
    SELECT [c].[UserId] [UserId], COUNT(1) [No]
    FROM [Comments] [c]
    WHERE [c].[CreationDate] >= @startDate
    GROUP BY [c].[UserId]
  ) [c1] ON [u].[Id] = [c1].[UserId]
  LEFT JOIN
  (
    SELECT [se].[UserId] [UserId], COUNT(1) [No]
    FROM [PostHistory] [se]
    WHERE
      [se].[CreationDate] >= @startDate AND
      [se].[PostHistoryTypeId] > 3 AND
      [se].[PostHistoryTypeId] < 7
    GROUP BY [se].[UserId]
  ) [se1] ON [u].[Id] = [se1].[UserId]
WHERE
  [u].[Reputation] >= 3000 AND
  [ph].[PostHistoryTypeId] = 10 AND -- 10 = "Post Closed"
  [p].[ClosedDate] >= @startDate
GROUP BY
  [u].[Id], [u].[DisplayName], [p1].[No], [c1].[No], [se1].[No]
ORDER BY
  [TotalClosedPostsInvolvedIn] DESC,
  COALESCE([c1].[No], 0) + COALESCE([se1].[No], 0) DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results