Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
-- Collect all reputation changes which might be influenced by the daily reputation limit; -- given downvotes to answers won't be accounted for, but the expected difference is rather small. WITH Reputation AS (SELECT v.CreationDate AS Date, 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, 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 = ##UserId:int## 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 UNION ALL -- suggested edits might influence the daily reputation limit as well SELECT DATEADD(DAY, DATEDIFF(DAY, 0, ApprovalDate), 0), -- date 2, -- old reputation 2 -- new reputation FROM SuggestedEdits AS se WHERE OwnerUserid = ##UserId:int## AND ApprovalDate IS NOT NULL AND ApprovalDate <= '2019-11-13' ) SELECT SUM(ReputationGained) AS 'Reputation gained' FROM -- We have to account for the daily reputation limit here: (SELECT (CASE WHEN SUM(NewReputation) > 200 THEN 200 ELSE SUM(NewReputation) END) - (CASE WHEN SUM(OldReputation) > 200 THEN 200 ELSE SUM(OldReputation) END) AS ReputationGained FROM Reputation GROUP BY Date) AS Temp