0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

SELECT UserId, DisplayName, Reputation,
  RANK() OVER (ORDER BY Reputation DESC),
    SUM(CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) AS NewRepEstimate,
    SUM(CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END) AS OldRepEstimate
  FROM
  (SELECT
    p.OwnerUserId AS UserId,
    v.CreationDate AS VoteDate,

  SUM(CASE
    WHEN p.PostTypeId = 1 -- question
     AND v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 5
    WHEN p.PostTypeId = 2 -- answer
     AND v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 10
    WHEN v.VoteTypeId = 3 -- downvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0
  END) AS OldReputation,

  SUM(CASE
    WHEN v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 10
    WHEN v.VoteTypeId = 3 -- downvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0
  END) AS NewReputation

    FROM Posts AS p
    INNER JOIN Votes AS v ON v.PostId = p.Id
    WHERE p.OwnerUserId IN (SELECT TOP 10 Id FROM Users ORDER BY Reputation DESC)

    GROUP BY p.OwnerUserId, v.CreationDate
  ) AS ReputationByDate
  INNER JOIN Users ON ReputationByDate.UserId = Users.Id
  GROUP BY UserId, DisplayName, Reputation

fork query

edited nov 21 19

created nov 21 19 Martin20528