Top users ranked by their average passive rep earned per day

2

Please login or register to vote for this query.

(click on this box to dismiss)

Top 100 users ranked by their average passive rep earned per answer and per day. Parameters o An answer becomes passive after "ignoreDays" days (default 60 days) o Must also be older than minAgeDays (compared with dump date). o Users must have at least 1000 rep and have posted 50 posts The output is the APY of passive upvotes for each user, averaged over the entirety of the user's activity, as well as averaged annual rentier income (that is, rep income from old posts). -------------------------------------------------------------------

English

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

---------------------------------------------------------------------
--
-- Top users ranked by their average passive rep earned per day per reputation point.
--
--  Parameters
--   o  An answer becomes passive after "ignoreDays" days
--   o  Must also be older than minAgeDays (compared with dump date).
-- 
---------------------------------------------------------------------

declare @ignoreDays numeric = 60
declare @minAgeDays numeric = 28
declare @minRep numeric = 1000
declare @minPosts numeric = 50
declare @latestDate datetime

select @latestDate = max(CreationDate) from Posts

declare @passiveRepPosts table (id int, score int, passiveDays int, OwnerUserId int)
insert @passiveRepPosts
select
    p.Id,
    score = sum(case when VoteTypeId = 2 then 10 when VoteTypeId = 3 then -2 else 0 end),
    datediff (day, p.CreationDate,  @latestDate) - @ignoreDays,
    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 p.ClosedDate is null
and datediff(day, p.CreationDate, v.CreationDate) > @ignoreDays
and datediff(day, p.CreationDate, @latestDate) > @minAgeDays
group by p.Id, p.OwnerUserId, p.CreationDate

declare @UserStats table (OwnerUserId int, answerCnt int, score float)
insert @UserStats
select
    u.Id,
    answerCnt = count ( p.Id ),
    scorePerDay = sum ( convert(decimal(8,3), p.Score) / convert(decimal(8,3), p.passiveDays ) )
from
    @passiveRepPosts p join Users u on u.Id = p.OwnerUserId
where
    u.Reputation > @minRep
group by
    u.Id

set nocount on

select
   u.id as [User Link],
   cast( u.Reputation as numeric(9,0)) as [User rep],
   cast( s.score / u.Reputation * 36500 as numeric(9,2)) as [APY from passive upvotes],
   cast( s.score * 365 as numeric(6,0)) as [Annual rentier income]
from
   @UserStats s,
   Users u
where u.Id = s.OwnerUserId
and answerCnt > @minPosts
order by [APY from passive upvotes] desc

Enter Parameters

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