6

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for cartographers, geographers and GIS professionals

with UData as (

select 
     u.Id as [UserId]
     , u.DisplayName [DisplayName]
     , datediff(dd, u.creationdate, GETDATE() ) as [Days]
     , count(a.Id) as [AnswerCount]  
     , (count(a.Id) * 1.0) / datediff(dd, u.creationdate, GETDATE()) as [AnswersPerDay]
     , avg(a.score * 1.0) as [ScorePerAnswer]
from Users u
inner join Posts a on a.OwnerUserId = u.Id
where a.PostTypeId=2
group by u.Id, u.creationdate, u.DisplayName
)
select TOP 500 Rank() Over (order by AnswersPerDay DESC) as [Rank],
       UserId as [User Link],
       DisplayName as [ForSort],
       Days,
       AnswerCount,
       cast(AnswersPerDay as DECIMAL(10,3)) as AnswersPerDay,
       cast(ScorePerAnswer as DECIMAL(10,3)) as ScorePerAnswer
from UData
where AnswerCount >= ##MinAnswers:int?10##
order by AnswersPerDay desc

fork query

edited sep 12 14 rolfl

created jan 6 14 jon.doe12549