Which users gained the most reputation by the 2019-11-13 change?

2

Please login or register to vote for this query.

(click on this box to dismiss)

Engineering

Q&A for professionals and students of engineering

-- It wouldn't be possible to calculate this for all users
WITH TopUsers AS (
  SELECT TOP (##numberOfHits:int?100## * 2) u.Id
    FROM Users AS u
    INNER JOIN Posts AS p
      ON p.OwnerUserId = u.Id
    WHERE p.PostTypeId = 1
      AND p.CommunityOwnedDate IS NULL
    GROUP BY u.Id
    ORDER BY SUM(p.Score) DESC
),

-- Collect all reputation changes which might be influenced by the daily reputation limit;
-- given downvotes to answers won't be accounted for, but the expected difference is rather small.
Reputation AS (
  SELECT
    p.OwnerUserId AS UserId,
    v.CreationDate AS Date,

  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,

  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

  UNION ALL
  
-- suggested edits might influence the daily reputation limit as well
SELECT
  OwnerUserid, -- user
  DATEADD(DAY, DATEDIFF(DAY, 0, ApprovalDate), 0), -- date
  2, -- old reputation
  2  -- new reputation
  FROM SuggestedEdits AS se
  WHERE OwnerUserid IN (SELECT Id FROM TopUsers)
    AND ApprovalDate IS NOT NULL
    AND ApprovalDate <= '2019-11-13'  
)

SELECT TOP ##numberOfHits:int?100## UserId AS [User Link],
  SUM(ReputationGained) AS 'Reputation gained'
  FROM
  (SELECT UserId,
    -- We have to account for the daily reputation limit here:
    (CASE WHEN SUM(NewReputation) > 200 THEN 200 ELSE SUM(NewReputation) END)
  - (CASE WHEN SUM(OldReputation) > 200 THEN 200 ELSE SUM(OldReputation) END)
    AS ReputationGained
    FROM Reputation GROUP BY UserId, Date) AS Temp
  GROUP BY UserId
  ORDER BY 2 DESC

Enter Parameters

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