I saw the new Mortarboard, Epic, and Legendary badges today, and was excited to see they were applied for past activity: retro-actively, as it were. But I was also bummed to see that I hadn't earned the Legendary badge yet. And then I thought back to all those months where I hit reputation cap every single weekday, almost without exception.. I must have gone nearly a year like that, and I thought that this just couldn't be right.
So I checked on it. The actual reputation calculation is complicated, and in fact the data dump doesn't include enough information to reproduce it accurately (you can't account for the -1 from making downvotes, for example). But using the October copy of the data dump this query returns 166143 candidate rows for my user id:
/***Number of Day you hit the Reputation Cap ***/
DECLARE @UserID int
Set @UserID = 3043;
WITH BaseQuery AS (
SELECT dateadd(dd,0, datediff(dd,0, v.CreationDate)) as Day, SUM(CASE when VoteTypeId=1 then 15 when VoteTypeId=2 then 10 else -2 end) as Reputation
FROM votes v
INNER JOIN posts p on p.Id=v.PostId And p.CommunityOwnedDate is null
WHERE v.VoteTypeId=2 and p.OwnerUserId=@UserID
GROUP BY dateadd(dd,0, datediff(dd,0, v.CreationDate))
HAVING SUM(CASE when VoteTypeId=1 then 15 when VoteTypeId=2 then 10 else -2 end) >= 200)
SELECT count(*) FROM BaseQuery
Update: The query changed to better account for accepted answers and CommunityWiki posts, so it now shows only 143 days.
If you add accepted answers and the two months of activity since then, I should have many more candidates as well. The main thing is that the number iswas above 150.
I know that due to ordering of upvotes and downvotes it's possible that on several of those days I ended up with a score less than 200; maybe even a lot of them. However, on each of those days I had at least 21 upvotes, and I don't see enough downvotes to account for 16 days worth of -10s. This means I hit the cap at least 150 times, even if I ended up slightly lower on some of them.
Is this a bug, or by design? Personally, I think it's a bug, because after all I did hit the cap on those days. Even if I ended up lower, I left reputation on the table for upvotes that weren't counted. That said, if I'm good at anything it's seeing both sides of a situation and I can see the "by design" argument. However, if it is by design, can we change the wording for the badges so that it says something more like "finished the day at or above the cap"?
