Money for Jam (modified)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Non-CW posts that earn the most passive reputation. Reputation in the first two weeks of post is ignored, all reputation after that is considered passive. Post must be at least four weeks old. This is a slightly modified version of the query no. 346 by krock.

English

Q&A for linguists, etymologists, and serious English language enthusiasts

-- Money for Jam
-- 
-- Non-CW posts that earn the most passive reputation.
-- Reputation in the first two weeks of post is ignored,
-- all reputation after that is considered passive.
-- Post must be at least four weeks old.
-- This is a slightly modified version of the query no. 346 by krock.

set nocount on

declare @latestDate datetime
select @latestDate = max(CreationDate) from Posts
declare @ignoreDays numeric = 14
declare @minAgeDays numeric = 28

declare @VoteStats table (PostId int, up int, down int, CreationDate datetime, OwnerUserId int)
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,
    p.OwnerUserId
from Votes v join posts p on v.postid = p.id
where v.VoteTypeId in (2,3)
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, p.OwnerUserId

set nocount off

select top 2000 PostId as [Post Link],
  OwnerUserId as [User Link],
  round(convert(decimal(10,2), up - down)/(datediff(day, vs.CreationDate, @latestDate) - @ignoreDays),3) as [Passive rep per day],
  (up - down) as [Passive rep],
  datediff(day, vs.CreationDate, @latestDate) - @ignoreDays as [Days counted]
from @VoteStats vs
order by [Passive Rep Per Day] desc

Enter Parameters

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