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 an approximate of user reputation
-- takes into account:
--  * votes up and down on answers and questions 
--  * accepts
--  * received bounties
--  * community wiki posts

DECLARE @UserID int = ##UserId##
DECLARE @DailyCap int = 200

create table #VotesCap1 (Id int primary key, Beneficiary int, Amount int, CreationDate datetime)

insert #VotesCap1
SELECT v.Id, u.Id, 
  (CASE v.VoteTypeId + 32*(p.PostTypeId + 32*(CASE WHEN v.CreationDate >= p.CommunityOwnedDate THEN 1 ELSE 0 END))
  WHEN 2+32*1 THEN 5   -- Question UP
  WHEN 2+32*2 THEN 10  -- Answer UP
  WHEN 3+32*1 THEN 0   -- Question DOWN
  WHEN 3+32*2 THEN -2  -- Answer DOWN
  ELSE 0
  END), v.CreationDate
FROM
  Votes v
  LEFT JOIN Posts p on v.PostId = p.Id
  LEFT JOIN Users u on p.OwnerUserId = u.Id
WHERE
  v.VoteTypeId = 2 or v.VoteTypeId = 3

  
create table #VotesCap2 (Id int primary key, Beneficiary int, Amount int, Total int, Capped1 int, Capped2 int)

insert #VotesCap2
SELECT v.Id, v.Beneficiary, v.Amount, SUM(w.Amount),
  CASE WHEN SUM(w.Amount) >= @DailyCap THEN 0 ELSE v.Amount END,
  CASE WHEN SUM(w.Amount) >= @DailyCap THEN 0 ELSE
  (@DailyCap-SUM(w.Amount)+v.Amount - ABS(@DailyCap-SUM(w.Amount)-v.Amount))/2 END
FROM
  #VotesCap1 v
  INNER JOIN #VotesCap1 w ON v.CreationDate > w.CreationDate
    AND CONVERT(int, v.CreationDate) = CONVERT(int, w.CreationDate)
GROUP BY
  v.Id, v.Beneficiary, v.Amount

create table #VotesReputation (Id int primary key, Beneficiary int, Amount int)

insert #VotesReputation
SELECT v.Id, u.Id, 
  (CASE
  WHEN v.VoteTypeId = 2 OR v.VoteTypeId = 3 THEN w.Amount
  WHEN v.VoteTypeId = 1 THEN 15  -- Answer Accept
  ELSE COALESCE(v.BountyAmount, 0)
  END)
FROM
  Votes v
  LEFT JOIN #VotesCap1 w on v.Id = w.Id
  LEFT JOIN Posts p on v.PostId = p.Id
  LEFT JOIN Users u on p.OwnerUserId = u.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