0

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 o Must also be older than minAgeDays (compared with dump date). o The column "Sum of all Answers Passive Score per Day" is the product of the number of qualifying answers by the average earned rep per answer and per day. -------------------------------------------------------------------

Q&A for anime and manga fans

---------------------------------------------------------------------
--
-- Top 100 users ranked by their average passive rep earned per answer 
-- and per day.
--
--  Parameters
--   o  An answer becomes passive after "ignoreDays" days
--   o  Must also be older than minAgeDays (compared with dump date).
--   o  The column "Sum of all Answers Passive Score per Day" is the 
--      product of the number of qualifying answers by the average 
--      earned rep per answer and per day.
---------------------------------------------------------------------

declare @ignoreDays numeric = 14
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(6,3), p.Score) / convert(decimal(6,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
   top 100 answerCnt as [Answer with Passive Votes],
   u.DisplayName as [Display name],
   cast( s.score as numeric(6,3)) as [Sum of all Answers Passive Score per Day],
   cast( (s.score / s.answerCnt) as numeric(6,3)) as [Average Answer Passive Score per Day]
from
   @UserStats s,
   Users u
where u.Id = s.OwnerUserId
and answerCnt > @minPosts
order by [Average Answer Passive Score per Day] desc

fork query

created may 19 11 alain.pannetier