Staying Power

10

Please login or register to vote for this query.

(click on this box to dismiss)

Total passive reputation gained from old posts 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.

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Staying Power
-- Total passive reputation gained from old posts
-- 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.
-- Mostly copied from http://data.stackexchange.com/stackoverflow/s/348/my-money-for-jam

set nocount on

declare @latestDate datetime
select @latestDate = max(CreationDate) from Posts
declare @ignoreDays numeric = 15
declare @minAgeDays numeric = @ignoreDays * 4
declare @UserId int = ##UserId##

-- temp table moded from http://data.stackexchange.com/stackoverflow/s/87
declare @VoteStats table (PostId int, up int, down int, CreationDate datetime)
insert @VoteStats
select
    p.Id,
    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),
    p.CreationDate
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)/(datediff(day, vs.CreationDate, @latestDate) - @ignoreDays)) as [Passive Rep Per Day],
  sum((up - down)) as [Passive Rep]
from @VoteStats vs

Enter Parameters

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