Q&A for folks dedicated to a lifestyle that can be maintained indefinitely without depleting available resources
-- cf https://data.stackexchange.com/stackoverflow/query/882556/reputation-gained-until-a-certain-date CREATE TABLE #changes (date datetime, reputation int); -- ANSWERS WITH abd AS (SELECT v.CreationDate AS VoteDate, -- Upvotes / downvotes received (downvotes given will be missing) SUM(CASE WHEN a.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 2 AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL) THEN 10 WHEN a.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 3 AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL) THEN -2 ELSE 0 END) AS ReputationFromVotes, -- Bounties earned SUM(CASE WHEN a.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 9 THEN v.BountyAmount ELSE 0 END) AS ReputationFromBounties, -- Answers accepted SUM(CASE WHEN q.OwnerUserId = ##UserId:int## AND a.OwnerUserId != ##UserId:int## AND v.VoteTypeId = 1 AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL) THEN 2 WHEN q.OwnerUserId != ##UserId:int## AND a.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 1 AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL) THEN 15 ELSE 0 END) AS ReputationFromAccepts FROM Posts AS a INNER JOIN Posts AS q ON a.ParentId = q.Id INNER JOIN Votes AS v ON v.PostId = a.Id WHERE a.OwnerUserId = ##UserId:int## OR q.OwnerUserId = ##UserId:int## GROUP BY v.CreationDate ), -- QUESTIONS qbd AS (SELECT v.CreationDate AS VoteDate, -- Upvotes / downvotes received SUM(CASE WHEN q.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 2 AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL) THEN 5 WHEN q.OwnerUserId = ##UserId:int## AND v.VoteTypeId = 3 AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL) THEN -2 ELSE 0 END) AS ReputationFromVotes FROM Posts AS q INNER JOIN Votes AS v ON v.PostId = q.Id WHERE q.OwnerUserId = ##UserId:int## AND q.PostTypeId = 1 GROUP BY v.CreationDate ), -- BOUNTIES bbd AS (SELECT v.CreationDate AS VoteDate, -- Bounties posted SUM(-v.BountyAmount) AS ReputationFromBounties FROM Votes AS v WHERE v.UserId = ##UserId:int## AND v.VoteTypeId = 8 GROUP BY v.CreationDate ), -- SUGGESTED EDITS ebd AS (SELECT DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0) AS VoteDate, -- Suggested edit approved (tag wiki suggested edits will be missing) 2 * COUNT(*) AS ReputationFromSuggestedEdits FROM SuggestedEdits AS se WHERE se.OwnerUserId = ##UserId## AND se.ApprovalDate IS NOT NULL GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0) ) -- Association bonus will be missing INSERT INTO #changes SELECT COALESCE(abd.VoteDate, qbd.VoteDate, bbd.VoteDate, ebd.VoteDate), SUM(CASE WHEN COALESCE(abd.ReputationFromVotes, 0) + COALESCE(qbd.ReputationFromVotes, 0) + COALESCE(ebd.ReputationFromSuggestedEdits, 0) > 200 THEN 200 ELSE COALESCE(abd.ReputationFromVotes, 0) + COALESCE(qbd.ReputationFromVotes, 0) + COALESCE(ebd.ReputationFromSuggestedEdits, 0) END + COALESCE(abd.ReputationFromBounties, 0) + COALESCE(abd.ReputationFromAccepts, 0) + COALESCE(bbd.ReputationFromBounties, 0)) FROM abd FULL OUTER JOIN qbd ON abd.VoteDate = qbd.VoteDate FULL OUTER JOIN bbd ON abd.VoteDate = bbd.VoteDate FULL OUTER JOIN ebd ON abd.VoteDate = ebd.VoteDate GROUP BY COALESCE(abd.VoteDate, qbd.VoteDate, bbd.VoteDate, ebd.VoteDate); SELECT p.Id AS [Post Link], p.CreationDate, SUM(c.Reputation) + CASE WHEN SUM(c.Reputation) >= 200 THEN 100 ELSE 0 END -- simulated association bonus FROM Posts AS p LEFT OUTER JOIN #changes AS c ON DATEADD(DAY, DATEDIFF(DAY, 0, p.CreationDate), 0) > c.Date WHERE p.PostTypeId = 1 -- Questions AND p.OwnerUserId = ##UserId:int## GROUP BY p.Id, p.CreationDate ORDER BY p.CreationDate; DROP TABLE #changes;