Approximate recalculation of user reputation

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

-- Calculates votes up/down on answers and questions plus bounties
DECLARE @UserID int = ##UserId##

create table #VotesReputation (Beneficiary int, Amount int)

insert #VotesReputation
SELECT u.Id, 
  (CASE v.VoteTypeId + 256*p.PostTypeId
  WHEN 2+256*1 THEN 5   -- Question UP
  WHEN 2+256*2 THEN 10  -- Answer UP
  WHEN 3+256*1 THEN 0   -- Question DOWN
  WHEN 3+256*2 THEN -2  -- Answer DOWN
  WHEN 1+256*2 THEN 15  -- Answer Accept
  ELSE COALESCE(v.BountyAmount, 0)
  END)
FROM 
  Users u
  LEFT JOIN Posts p on p.OwnerUserId = u.Id
  LEFT JOIN Votes v on v.PostId = p.Id

SELECT TOP 20 
  u.DisplayName, 
  u.Id, 
  u.Reputation, 
  SUM(r.Amount) as Recalculated, 
  u.Reputation - SUM(r.Amount) as Diff
FROM #VotesReputation r
  INNER JOIN Users u ON u.Id = r.Beneficiary
GROUP BY u.Id, u.DisplayName, u.Reputation
ORDER BY
  CASE u.Id WHEN @UserId THEN 1 ELSE 0 END DESC,
  ABS(u.Reputation - SUM(r.Amount)) DESC

Enter Parameters

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