Total answers and sum of answers for questions per MM/YY

0

Please login or register to vote for this query.

(click on this box to dismiss)

A break down of answer per MM/YY

Stack Overflow

Q&A for programmers

-- Total answers and sum of answers for questions per MM/YY
-- A break down of answer per MM/YY

WITH questions(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 1
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answers(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 2
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answers_total (yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), SUM(AnswerCount)
   FROM Posts
   WHERE PostTypeId = 1
   GROUP BY Year(CreationDate), Month(CreationDate)
)
SELECT q.yr AS Year, q.mth AS Month, a.cnt AS Answers, at.cnt AS TotalAnswers
FROM questions q
    JOIN answers a ON a.yr = q.yr AND a.mth = q.mth
    JOIN answers_total at ON at.yr = q.yr AND at.mth = q.mth
ORDER BY q.yr DESC, q.mth DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached