Q&A for professionals and students of engineering
SELECT UserId, DisplayName, Reputation, RANK() OVER (ORDER BY Reputation DESC), SUM(CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) AS NewRepEstimate, SUM(CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END) AS OldRepEstimate 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, DisplayName, Reputation