Q&A about the site for sound designers
WITH rawData(uid,userrep,uncapped,capped,accepts,bounties) AS( SELECT tmp.[User Link], tmp.[Reputation], SUM(tmp.voteTotal) AS [Uncapped Votes], SUM(CASE WHEN tmp.voteTotal >= 200 THEN 200 ELSE tmp.voteTotal END ) AS [Capped Votes], SUM(tmp.acceptTotal) AS [Accepts], SUM(tmp.bountyTotal) AS [Bounties] -- CAST(100.0 * SUM(tmp.acceptTotal) / tmp.[Reputation] AS DECIMAL(20, 3)) AS [% from Accepts] FROM (SELECT v.CreationDate, COUNT(v.Id) AS votecount, p.OwnerUserId AS [User Link], u.Reputation AS [Reputation], SUM((CASE WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5 -- Upvoted question WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10 -- Upvoted answer WHEN VoteTypeId = 3 THEN -2 -- Downvote ELSE 0 END)) AS voteTotal, SUM((CASE WHEN v.CreationDate > p.CommunityOwnedDate THEN 0 -- After Community Wiki WHEN v.VoteTypeId = 1 THEN 15 -- Accepted answer ELSE 0 END)) AS acceptTotal, SUM((CASE WHEN v.VoteTypeId = 9 THEN BountyAmount -- Earned Bounty ELSE 0 END)) AS bountyTotal FROM Votes v JOIN VoteTypes vt ON vt.Id = v.VoteTypeId JOIN Posts p ON p.Id = v.PostId JOIN Users u ON u.Id = p.OwnerUserId WHERE p.OwnerUserId is not null and u.Reputation = 1 GROUP BY u.Reputation, v.CreationDate, p.OwnerUserId ) AS tmp GROUP BY tmp.[User Link], tmp.[Reputation] ) SELECT TOP 1000 uid AS [User Link], userrep AS [Reputation], capped + accepts + bounties AS [DE Recalced Rep], uncapped AS [Uncapped Votes], capped AS [Capped Votes], accepts AS [Accepts], bounties AS [Bounties] FROM rawData ORDER BY [DE Recalced Rep] DESC