Top users before and after reputation change

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

WITH TopUsers AS (
  SELECT TOP 20 Id
    FROM Users
    ORDER BY Reputation DESC
),

ReputationByDate AS (
  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 Id FROM TopUsers)
      AND v.CreationDate <= '2019-11-13' -- vote times are blanked,
      -- let's consider all votes cast on 2019-11-13 since the change went live gradually

    GROUP BY p.OwnerUserId, v.CreationDate
)

SELECT UserId AS [User Link], Reputation AS 'New reputation',
  Reputation -
       SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END)
         - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END))
         AS 'Old reputation'
  FROM ReputationByDate
  INNER JOIN Users ON ReputationByDate.UserId = Users.Id
  GROUP BY UserId, Reputation
  ORDER BY 2 DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results