Total Answers/Month & ~Answers/Day for a User

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

-- This query computes the number of answers & scores with those answers for a single user 
--  easily amendable to questions by changing the PostTypeId to 1
-- Note that the CURRENT month may be a little skewed from averages due to SE
--  uploads lagging and/or if it's not the end of month 


SELECT [Month], 
       COUNT(*) AS [Answers/Month],
       CAST(CAST(COUNT(*) AS FLOAT)/DAY(EOMONTH(Month)) AS NUMERIC(10,3)) AS [Answers/Day],
       SUM(Score) AS [Score/Month],
       CAST(SUM(Score)/CAST(COUNT(*) AS FLOAT) AS NUMERIC(10,3)) AS [Score/Answer]

FROM(
  SELECT CAST( CAST(DATEPART(YYYY, CreationDate) AS varchar) + '-' 
             + CAST(DATEPART(MM, CreationDate) AS varchar) + '-01' AS DateTime) [Month], 
         Score
  FROM Posts 
  WHERE OwnerUserId = ##UserId:int## AND
        PostTypeId = 2
) AS X


GROUP BY [Month]
ORDER BY [Month] DESC

Enter Parameters

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