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 designers and users of artificial or invented languages, consciously developed instead of having developed naturally
WITH cte1 AS ( SELECT CAST(Votes.CreationDate AS DATE) AS VoteDate , CASE 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 , BountyAmount FROM Posts 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 ( SELECT VoteDate , SUM(CASE WHEN VoteType = 'Answer' THEN 10 WHEN VoteType = 'Question' THEN 10 ELSE 0 END ) AS CappedRep , SUM(CASE WHEN VoteType = 'Accept' THEN 15 WHEN VoteType = 'Bounty' THEN BountyAmount ELSE 0 END ) 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 FROM cte1 GROUP BY VoteDate ) SELECT ROW_NUMBER() OVER (ORDER BY VoteDate) AS Num , VoteDate , CappedRep , UncappedRep , CappedRep + UncappedRep AS Total , Summary FROM cte2 WHERE CappedRep + UncappedRep >= 200 ORDER BY VoteDate DESC