0

Please login or register to vote for this query.

(click on this box to dismiss)

This was originally posted at http://data.stackexchange.com/programmers/q/69957/top-rated-users-if-there-was-no-reputation-cap There are 4 ways that reputation can be lost: Reputation Cap, Community Wiki Bounty awards, and Posts migrations. This separates out Community Wiki AND votes lost prior to the latest migration. As yet it does not detect rep lost to bounty awards (bounty awarder Id is scrubbed away because it's in the votes table).

Q&A for anime and manga fans

-- Top rated users with no reputation cap or Community Wiki
-- This was originally posted at http://data.stackexchange.com/programmers/q/69957/top-rated-users-if-there-was-no-reputation-cap
-- 
-- There are 4 ways that reputation can be lost: Reputation Cap, Community Wiki
-- Bounty awards, and Posts migrations. This separates out Community Wiki, but
-- I still have not found a solution to detect migrated post dates or bounty
-- awards (bounty awarder Id is scrubbed away because it's in the votes table).

WITH VotePoints AS (
  SELECT
    Votes.PostId,
    Votes.CreationDate,
    Posts.OwnerUserId AS UserId,
    VoteTypeId,
    (CASE
      WHEN VoteTypeId = 1 THEN 15                    -- Accepted answer
      WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5  -- Upvoted question
      WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10 -- Upvoted answer
      WHEN VoteTypeId = 3 THEN -2                    -- Downvote
      WHEN VoteTypeId = 9 THEN BountyAmount          -- Earned Bounty
    END) AS Points,
    (CASE
      WHEN Votes.CreationDate > Posts.CommunityOwnedDate
      THEN 1 ELSE 0
    END) AS IsCommunityOwned,
    (CASE
      WHEN (SELECT TOP 1 CreationDate FROM PostHistory WHERE PostHistoryTypeId=17 AND PostHistory.PostId = Votes.PostId ORDER BY CreationDate DESC) > Votes.CreationDate
      THEN 1 ELSE 0
    END) AS IsBeforeMigration
  FROM
    Votes
  INNER JOIN
    Posts ON Posts.Id = Votes.PostId
)

, CalculatedReputation AS (
  SELECT
    Users.Id AS UserId,
    SUM(Points) AS Total,
    SUM(CASE
        WHEN (VoteTypeId = 2 OR VoteTypeId = 3 OR VoteTypeId = 3)
        AND IsCommunityOwned = 1 AND IsBeforeMigration = 0
        THEN Points ELSE 0 END) AS LostWiki,
    SUM(CASE
        WHEN IsBeforeMigration = 1
        THEN Points ELSE 0 END) AS LostMigration
  FROM
    VotePoints
  INNER JOIN
    Users ON Users.Id = UserId
  GROUP BY
    Users.Id,
    Users.Reputation
)

SELECT TOP 1000
  Users.Id as [User Link],
  Users.Reputation,
  Total,
  Total - LostWiki - LostMigration as Uncap,
  Total - Users.Reputation as Lost,
  LostWiki,
  LostMigration AS LostMig
FROM
  CalculatedReputation
INNER JOIN Users ON Users.Id = UserId
ORDER BY
  Uncap DESC

fork query

created feb 15 11 Renesis