Top-reputation users by inverse voting calculation

1

Please login or register to vote for this query.

(click on this box to dismiss)

Accept: +15/+2, upvote: -2, downvote: +10/+5. Ideal method to find the best users of meta sites.

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT
    Posts.Id,
    MIN(Posts.OwnerUserId) AS OwnerUserId,
    SUM(
      CASE
        WHEN Posts.PostTypeId = 1 AND Votes.VoteTypeId = 2 THEN -2
        WHEN Posts.PostTypeId = 1 AND Votes.VoteTypeId = 3 THEN 5
        WHEN Posts.PostTypeId = 2 AND Votes.VoteTypeId = 1 THEN 15
        WHEN Posts.PostTypeId = 2 AND Votes.VoteTypeId = 2 THEN -2
        WHEN Posts.PostTypeId = 2 AND Votes.VoteTypeId = 3 THEN 10
        ELSE 0 -- unreachable
      END
    ) +
    MAX(CASE WHEN Posts.AcceptedAnswerId IS NOT NULL THEN 2 ELSE 0 END) AS Rep
  FROM Posts, Votes
  WHERE
    Posts.PostTypeId BETWEEN 1 AND 2
    AND
    Votes.VoteTypeId BETWEEN 1 AND 3
    AND
    Votes.PostId = Posts.Id
    AND
    Posts.OwnerUserId > 0
  GROUP BY Posts.Id
)
SELECT TOP 1000
  DENSE_RANK() OVER (ORDER BY SUM(Rep) DESC) AS Rank,
  OwnerUserId AS [User Link],
  SUM(Rep) AS Rep
FROM Raw
GROUP BY OwnerUserId
ORDER BY Rep DESC, OwnerUserId

Enter Parameters

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