Q&A for professionals, academics, and students working within the systems development life cycle
SELECT UserId, Reputation AS NewReputation, Reputation - SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)) AS OldReputation 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) AND v.CreationDate <= '2019-11-13' -- vote times are blanked, -- let's consider all votes cast on 2019-11-13 since the change went live gradually GROUP BY p.OwnerUserId, v.CreationDate ) AS ReputationByDate INNER JOIN Users ON ReputationByDate.UserId = Users.Id GROUP BY UserId, Reputation