Ranks users by most "passive" rep they gained in the last 30 days.
Q&A for professional and enthusiast programmers
-- Ranks users by most "passive" rep they gained in the last 30 days. -- "Passive Rep" is rep gained from posts that are older than 30 days. -- "Active Rep" is rep gained from posts that are younger than 30 days. -- Legend: -- Passive Votes / Day: Votes received on old posts in the last 30 days. -- Active Votes / Day: Votes received on new posts in the last 30 days. -- Passive Rep / Day: Rep received* on old posts in the last 30 days. -- Active Rep / Day: Rep received* on new posts in the last 30 days. -- *Does not take into account for the repcap. -- History: -- Written by Mysticial (http://stackoverflow.com/users/922184/mysticial) -- Optimized by wilx (http://stackoverflow.com/users/341065/wilx) declare @window numeric = 180 declare @ageThreshold numeric = 30 declare @lastDate DATETIME SET @lastDate = (SELECT MAX(CreationDate) FROM Votes); --declare @rawTable table (id int, pv int, av int, pr int, ar int) --insert @rawTable WITH rawTable(id, pv, av, pr, ar) AS ( SELECT TOP 500 tmp.[User Link], SUM(tmp.pv) AS [Passive Votes], SUM(tmp.av) AS [Active Votes], SUM(tmp.pr) AS [Passive Rep], SUM(tmp.ar) AS [Active Rep] FROM -- This sub-query grabs a table of posts with passive/active/votes/rep columns. -- The outer query then collapses all of these by user ID. (SELECT Posts.OwnerUserId AS "User Link", SUM(CASE WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) < @ageThreshold THEN 0 WHEN VoteTypeId = 2 THEN 1 WHEN VoteTypeId = 3 THEN -1 END) AS pv, SUM(CASE WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) >= @ageThreshold THEN 0 WHEN VoteTypeId = 2 THEN 1 WHEN VoteTypeId = 3 THEN -1 END) AS av, SUM(CASE WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) < @ageThreshold THEN 0 WHEN VoteTypeId = 9 THEN BountyAmount -- Earned Bounty WHEN Votes.CreationDate > Posts.CommunityOwnedDate THEN 0 -- After Community Wiki WHEN VoteTypeId = 1 THEN 15 -- Accepted answer 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 END) AS pr, SUM(CASE WHEN DATEDIFF(DAY,Posts.CreationDate,Votes.CreationDate) >= @ageThreshold THEN 0 WHEN VoteTypeId = 9 THEN BountyAmount -- Earned Bounty WHEN Votes.CreationDate > Posts.CommunityOwnedDate THEN 0 -- After Community Wiki WHEN VoteTypeId = 1 THEN 15 -- Accepted answer 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 END) AS ar FROM Votes INNER JOIN Posts ON Posts.Id = Votes.PostId WHERE Posts.OwnerUserId is not null and DATEDIFF(DAY,Votes.CreationDate,@lastDate) <= @window GROUP BY Posts.OwnerUserID ) AS tmp GROUP BY tmp.[User Link], tmp.pv, tmp.av, tmp.pr, tmp.ar ORDER BY [Passive Rep] DESC ), -- Create user table with account age and post count. --declare @userTable table (id int, start DATETIME, posts int) --insert @userTable userTable (id, start, posts) AS ( SELECT u.Id AS [User Link], u.CreationDate as start, Count(*) AS [Posts] FROM Posts p INNER JOIN Users u ON u.Id = p.OwnerUserId WHERE p.OwnerUserId is not null and p.posttypeid = 1 or p.posttypeid = 2 GROUP BY u.Id, u.CreationDate ) -- Join everything together SELECT TOP 1000 row_number() over(order by t.pr desc) as [#], t.id AS [User Link], CONVERT(VARCHAR(10),u.start,102) AS [Start Date], u.posts AS [Posts], -- t.pv AS [Passive Votes], Cast(t.pv / @window AS DECIMAL(20, 3)) as [Passive Votes / Day], -- t.av AS [Active Votes], Cast(t.av / @window AS DECIMAL(20, 3)) as [Active Votes / Day], -- t.pr AS [Passive Rep], Cast(t.pr / @window AS DECIMAL(20, 3)) as [Passive Rep / Day], -- t.ar AS [Active Rep] Cast(t.ar / @window AS DECIMAL(20, 3)) as [Active Rep / Day] FROM rawTable t INNER JOIN userTable u ON t.id = u.id ORDER BY [Passive Rep / Day] DESC