Answered/Unanswered Questions by Month

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

DECLARE @beginDate DATETIME = '2010-01-01'
DECLARE @endDate DATETIME = GETDATE()

SELECT T.Month,
  T.QuestionCount,
  (T.QuestionCount - T.UnansweredCount) AS AnsweredCount,
  T.UnansweredCount,
  (T.QuestionCount -  T.UnansweredCount)*400000.0 / CAST(T.QuestionCount AS DECIMAL) AS "AnswerRate (scaled by 400000)"
FROM
(SELECT 
  CAST(
    CAST(DATEPART(YEAR, CreationDate) AS NVARCHAR) + '-' + 
    CAST(DATEPART(MONTH, CreationDate) AS NVARCHAR) + '-' +
    CAST(DATEPART(DAY, CreationDate) as NVARCHAR)
      AS DATETIME) AS 'Month',
  SUM(CAST (
    CASE 
       WHEN AnswerCount = 0
         THEN 1 
         ELSE 0 
       END AS INT)) AS 'UnansweredCount',
  COUNT(*) AS 'QuestionCount'
FROM Posts p
  INNER JOIN PostTags pt ON p.Id = pt.PostId
  INNER JOIN Tags t ON t.Id = pt.TagId
  WHERE (p.PostTypeId = 1) AND (p.CreationDate BETWEEN @beginDate AND @endDate)
GROUP BY 
  DATEPART(YEAR, CreationDate), 
  DATEPART(MONTH, CreationDate),
  DATEPART(DAY, CreationDate)) T
ORDER BY T.Month DESC

Enter Parameters

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