Tells you how often you hit the reputation cap (50 = Epic Badge, 150 Legendary Badge)
Q&A for cartographers, geographers and GIS professionals
-- Enter Query Title -- Enter Query Description DECLARE @UserId int = ##UserId## SELECT SUM(Overvoted.SumVotes) AS 'Hit The Reputation Cap' FROM ( SELECT SumVotes = CASE WHEN SUM(AllVotes.Votes) >= 200 THEN 1 ELSE 0 END FROM (SELECT COUNT(*) * 10 as Votes, Votes.CreationDate AS CreationDate FROM Posts INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 2 WHERE Posts.OwnerUserId = @UserId AND Posts.CommunityOwnedDate IS NULL GROUP BY Votes.CreationDate UNION SELECT COUNT(*) * -2 as Votes, Votes.CreationDate AS CreationDate FROM Posts INNER JOIN Votes ON Votes.PostId = Posts.Id and VoteTypeId = 3 WHERE Posts.OwnerUserId = @UserId AND Posts.CommunityOwnedDate IS NULL GROUP BY Votes.CreationDate) as AllVotes GROUP BY AllVotes.CreationDate ) as OverVoted