Decadist badge: Collected at least 200 rep from post votes in each year of the last 10

2

Please login or register to vote for this query.

(click on this box to dismiss)

Not exact, we can't count a lot of things affecting the reputation, but it is about roughly this. Check this [meta discussion](https://meta.stackoverflow.com/q/374646/1783163).

Stack Overflow

Q&A for professional and enthusiast programmers

WITH VoteRep AS (
  SELECT 1 AS VoteTypeId, 2 AS PostTypeId, 15 AS Rep
  UNION
  SELECT 2 AS VoteTypeId, 1 AS PostTypeId, 5 AS Rep
  UNION
  SELECT 2 AS VoteTypeId, 2 AS PostTypeId, 10 AS Rep
  UNION
  SELECT 3 AS VoteTypeId, 1 AS PostTypeId, -2 AS Rep
  UNION
  SELECT 3 AS VoteTypeId, 2 AS PostTypeId, -2 AS Rep
),
Raw AS (
  SELECT
    VoteRep.Rep,
    Posts.OwnerUserId AS UserId,
    DATEDIFF(yy, Votes.CreationDate, CURRENT_TIMESTAMP) AS VoteAge
  FROM Posts, Votes, VoteRep
  WHERE
    Votes.PostId = Posts.Id
    AND
    Votes.VoteTypeId = VoteRep.VoteTypeId
    AND
    Posts.PostTypeId = VoteRep.PostTypeId
    AND
    (
      Posts.CommunityOwnedDate IS NULL
      OR
      Votes.CreationDate < Posts.CommunityOwnedDate
    )
),
Raw2 AS (
  SELECT SUM(Rep) AS Rep, VoteAge, UserId
  FROM Raw
  GROUP BY UserId, VoteAge
  HAVING SUM(Rep) >= 200 AND VoteAge <= 9
),
Raw3 AS (
  SELECT UserId
  FROM Raw2
  GROUP BY UserId
  HAVING COUNT(*) = 10
)
SELECT Raw3.UserId AS [User Link], Users.*
FROM Raw3, Users
WHERE Raw3.UserId = Users.Id AND Users.Id >= 1
ORDER BY Users.Reputation DESC, Users.Id

Enter Parameters

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