Most prolific answerers during date interval, mean answer score

2

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

declare @EndDate datetime = (select case
  -- If we request 'today', actually use the date
  -- of the most recent transfer to this database,
  -- which may be as much as a week in the past.
  when lower(##EndDate:string?today##) = 'today' then max(CreationDate)
  -- Otherwise, specify the most recent date to include.
  else ##EndDate##
  end
  from Posts
);
  
declare @StartDate datetime = dateadd(day, -1*##DaysBack?30##, @EndDate);

SELECT top ##HowManyUsers?50##
OwnerUserId as [User Link],
COUNT(*) as Answers,
Round(1.0 * count(*) / ##DaysBack##, 1) as 'Answers per day',
Round(1.0 * sum(Score) / Count(*), 2) as 'Mean Answer Score',
'query://868495?userId=' + cast(OwnerUserId as nvarchar) 
+ '&EndDate=' + cast(##EndDate## as nvarchar)
+ '&DaysBack=' + cast(##DaysBack## as nvarchar)
+ '#graph|link'
as 'Answer Score Distribution'
FROM Posts
WHERE CreationDate between @StartDate and @EndDate
  AND PostTypeId = -- 1 for questions, 2 for answers
  (select case
    when lower(##SecretlyUseQuestions:string?no##) = 'yes' then 1
    else 2
    end)
GROUP BY OwnerUserId
ORDER BY Answers DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results