2

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited nov 13

created mar 1 Salman.Arshad