Calculates the amount of passive reputation that a user earns per day (on average).
Reputation gained in the first 15 days of post is ignored,
all reputation after that is considered passive reputation.
Post must be at least 60 Days old.
Q&A for professional and enthusiast programmers
-- My Money for Jam
-- My Non Community Wiki Posts that earn the most Passive Reputation.
-- Reputation gained in the first 15 days of post is ignored,
-- all reputation after that is considered passive reputation.
-- Post must be at least 60 Days old.
set nocount on
declare @latestDate datetime
select @latestDate = max(CreationDate) from Posts
declare @ignoreDays numeric = 60
declare @minAgeDays numeric = 60
declare @UserId int = ##UserId##
-- temp table moded from http://odata.stackexchange.com/stackoverflow/s/87
declare @VoteStats table (PostId int, up int, down int, CreationDate datetime)
up = sum(case when VoteTypeId = 2 then
case when p.ParentId is null then 5 else 10 end
else 0 end),
down = sum(case when VoteTypeId = 3 then 2 else 0 end),
from Votes v join posts p on v.postid = p.id
where v.VoteTypeId in (2,3)
and OwnerUserId = @UserId
and p.CommunityOwnedDate is null
and datediff(day, p.CreationDate, v.CreationDate) > @ignoreDays
and datediff(day, p.CreationDate, @latestDate) > @minAgeDays
group by p.Id, p.CreationDate, p.ParentId
set nocount off
select SUM(convert(decimal(10,2), up - down)/(1)) as [Passive Rep Per Day]
from @VoteStats vs