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

create table #Recalculated (Id int primary key, Calc int)

insert #Recalculated
SELECT u.Id as Id, SUM(
  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
  ELSE COALESCE(v.BountyAmount, 0)
  END) as Calc
FROM 
  Users u
  LEFT JOIN Posts p on p.OwnerUserId = u.Id
  LEFT JOIN Votes v on v.PostId = p.Id  
GROUP BY
  u.Id, u.Reputation
ORDER BY
  u.Reputation DESC

SELECT TOP 20 
  -- u.displayname, 
  -- r.Id, 
  u.Reputation, 
  r.Calc, 
  u.Reputation - r.Calc as Diff
FROM #Recalculated r
  INNER JOIN Users u ON u.Id = r.Id
ORDER BY
  ABS(u.Reputation - r.Calc) DESC

Enter Parameters

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