Returns list of dates where user earned 200 or more rep. These events and are counted in your progress towards epic and legendary badges.
- Only positive reputation changes are counted.
- Answer and question upvotes are counted as +10 and +10 respectively.
Todo: does not count reputation earned by accepting answers and suggested edits.
Notes: retracted votes are not counted on either day (casted day and retracted day). The results may not match the reputation shown on reputation tab for those two days and the count could be less that the count shown on badge progress widget. Retracted votes, apparently, do not decrease the progress if the vote has "settled". See https://meta.stackoverflow.com/q/376997/87015
Q&A for professional and enthusiast programmers
WITH cte1 AS (
SELECT CAST(Votes.CreationDate AS DATE) AS VoteDate
WHEN Votes.VoteTypeId = 1 AND Posts.PostTypeId = 2 THEN 'Accept'
WHEN Votes.VoteTypeId = 2 AND Posts.PostTypeId = 1 THEN 'Question'
WHEN Votes.VoteTypeId = 2 AND Posts.PostTypeId = 2 THEN 'Answer'
WHEN Votes.VoteTypeId = 9 THEN 'Bounty'
END AS VoteType
JOIN Votes ON Posts.Id = Votes.PostId
WHERE Posts.OwnerUserId = ##UserId:int##
AND Votes.VoteTypeId IN (1, 2, 9)
AND Votes.CreationDate < COALESCE(Posts.CommunityOwnedDate, '9999-12-31')
), cte2 AS (
WHEN VoteType = 'Answer' THEN 10
WHEN VoteType = 'Question' THEN 10
) AS CappedRep
WHEN VoteType = 'Accept' THEN 15
WHEN VoteType = 'Bounty' THEN BountyAmount
) AS UncappedRep
, CONCAT_WS(' + '
, CAST(SUM(CASE WHEN VoteType = 'Answer' THEN 1 END) AS VARCHAR(10)) + ' x 10'
, CAST(SUM(CASE WHEN VoteType = 'Question' THEN 1 END) AS VARCHAR(10)) + ' x 5'
, CAST(SUM(CASE WHEN VoteType = 'Accept' THEN 1 END) AS VARCHAR(10)) + ' x 15'
, SUM(CASE WHEN VoteType = 'Bounty' THEN BountyAmount END)
) AS Summary
GROUP BY VoteDate
SELECT ROW_NUMBER() OVER (ORDER BY VoteDate) AS Num
, CappedRep + UncappedRep AS Total
WHERE CappedRep + UncappedRep >= 200
ORDER BY VoteDate DESC