1

Please login or register to vote for this query.

(click on this box to dismiss)

Lists the days that your cappable reputation exceeded the reputation cap. Known shortcoming: The vote times are erased, so every vote is logged as being at midnight UTC. This screws up the counting of votes after a post is auto-converted to Community Wiki. This means you will get credit for votes received on community wiki posts until the end of the day that they were converted to community wiki. Votes after that day will be correctly counted as community wiki, giving you no reputation.

Q&A for Drupal developers and administrators

-- On what days did I hit the reputation cap?
-- An attempt to make a query showing the days on which the reputation cap was
-- hit for a given user.
-- (Updated to remove votes on CW posts from the score)

WITH VotePoints AS (
  SELECT
    Votes.PostId,
    Votes.CreationDate,
    Posts.OwnerUserId AS UserId,
    VoteTypeId,
    (CASE
      WHEN VoteTypeId = 1 THEN 15                    -- Accepted answer
      WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5  -- Upvoted question
      WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10 -- Upvoted answer
      WHEN VoteTypeId = 3 THEN -2                    -- Downvote
      WHEN VoteTypeId = 9 THEN BountyAmount          -- Earned Bounty
    END) AS Points,
    (CASE WHEN VoteTypeId = 2 OR VoteTypeId = 3
       THEN 1 ELSE 0
    END) AS IsCappable,
    (CASE
      WHEN Votes.CreationDate > Posts.CommunityOwnedDate
      THEN 1 ELSE 0
    END) AS IsCommunityOwned,
    (CASE
      WHEN (SELECT TOP 1 CreationDate FROM PostHistory WHERE PostHistoryTypeId=17 AND PostHistory.PostId = Votes.PostId ORDER BY CreationDate DESC) > Votes.CreationDate
      THEN 1 ELSE 0
    END) AS IsBeforeMigration
  FROM
    Votes
  INNER JOIN
    Posts ON Posts.Id = Votes.PostId
)

SELECT
  CreationDate,
  SUM((CASE
    WHEN IsCappable = 1 AND IsCommunityOwned = 0 AND IsBeforeMigration = 0
    THEN Points ELSE 0
  END)) AS CappableScore,
  SUM(Points) AS TotalScore
FROM VotePoints
WHERE UserId = ##UserId##
GROUP BY CreationDate
HAVING SUM((CASE
    WHEN IsCappable = 1 AND IsCommunityOwned = 0 AND IsBeforeMigration = 0
    THEN Points ELSE 0
  END)) > 200
ORDER BY CreationDate DESC

fork query

created feb 15 11 Renesis