Q&A for Ubuntu users and developers
SELECT TOP 100 tmp2.[Date], tmp2.[User Link], tmp2.[Votes], tmp2.[Total Rep Gain] / tmp2.[Votes] AS [Ratio] FROM (SELECT tmp.[Date] AS [Date], tmp.[User Link] AS [User Link], tmp.[Before Cap Vote] AS [Uncapped Votes], tmp.[Accept] AS [Accepts], tmp.[Bounty] AS [Bounties], tmp.[Votes] AS [Votes], (CASE WHEN tmp.[Before Cap Vote] > 200 THEN 200 ELSE tmp.[Before Cap Vote] END) + tmp.[Accept] + tmp.[Bounty] AS [Total Rep Gain] FROM (SELECT v.CreationDate AS [Date], p.OwnerUserId AS [User Link], COUNT(v.Id) AS [Votes], Sum(CASE WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 WHEN v.VoteTypeId = 2 and p.PostTypeId = 1 THEN 5 WHEN v.VoteTypeId = 2 and p.PostTypeId = 2 THEN 10 WHEN v.VoteTypeId = 3 THEN -2 ELSE 0 END ) AS [Before Cap Vote], Sum(CASE WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 WHEN v.VoteTypeId = 1 THEN 15 ELSE 0 END ) AS [Accept], Sum(CASE WHEN v.VoteTypeId = 9 THEN BountyAmount ELSE 0 END ) AS [Bounty] FROM Votes v JOIN VoteTypes vt ON vt.Id = v.VoteTypeId JOIN Posts p ON p.Id = v.PostId WHERE p.OwnerUserId is not null GROUP BY v.CreationDate, p.OwnerUserId ) AS tmp ) AS tmp2 ORDER BY [Ratio] DESC, [Votes] DESC