Reputation cap cut off

10

Please login or register to vote for this query.

(click on this box to dismiss)

This query calculates how much reputation has been cut off because you reached the limit of 200 reputation per day (through upvotes).

Code Golf and Coding Challenges

Q&A for programming puzzle enthusiasts and code golfers

-- Reputation cap cut off
-- This query calculates how much reputation has been cut off because
-- you reached the limit of 200 reputation per day (through upvotes).
DECLARE @UserId int = ##UserId##

SELECT SUM(Overvoted.SumVotes) FROM
(
SELECT SumVotes = CASE
                    WHEN SUM(AllVotes.Votes) >= 200 THEN
                    SUM(AllVotes.Votes) - 200
                    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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results