Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games
SELECT UserId AS [User Link], RANK() OVER (ORDER BY Reputation DESC) AS 'New rank', Reputation AS 'New reputation', RANK() OVER (ORDER BY Reputation - SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)) DESC) AS 'Old rank', Reputation - SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)) AS 'Old reputation' FROM ( SELECT p.OwnerUserId AS UserId, v.CreationDate AS VoteDate, SUM(CASE WHEN p.PostTypeId = 1 -- question AND v.VoteTypeId = 2 -- upvote AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL) THEN 5 WHEN p.PostTypeId = 2 -- answer AND v.VoteTypeId = 2 -- upvote AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL) THEN 10 WHEN v.VoteTypeId = 3 -- downvote AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL) THEN -2 ELSE 0 END) AS OldReputation, SUM(CASE WHEN v.VoteTypeId = 2 -- upvote AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL) THEN 10 WHEN v.VoteTypeId = 3 -- downvote AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL) THEN -2 ELSE 0 END) AS NewReputation FROM Posts AS p INNER JOIN Votes AS v ON v.PostId = p.Id WHERE p.OwnerUserId IN (SELECT TOP 10 Id FROM Users ORDER BY Reputation DESC) GROUP BY p.OwnerUserId, v.CreationDate ) AS ReputationByDate INNER JOIN Users ON ReputationByDate.UserId = Users.Id GROUP BY UserId, Reputation ORDER BY 2