0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those using, extending or developing Emacs

WITH MonthlyQuestions AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfQuestions
  FROM
    Posts
  WHERE
    PostTypeId = 1 -- Question
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
),
MonthlyAnswers AS (
  SELECT
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0) AS Month,
    COUNT(*) AS NumberOfAnswers
  FROM
    Posts
  WHERE
    PostTypeId = 2 -- Answer
  GROUP BY
    DATEADD(month, DATEDIFF(month, 0, CreationDate), 0)
),
Months as (
  SELECT 
     Month
  FROM MonthlyQuestions
  UNION
  SELECT 
     Month
  FROM MonthlyAnswers
)
SELECT
  m.Month,
  mq.NumberOfQuestions,
  ma.NumberOfAnswers
FROM
  Months m
LEFT JOIN
  MonthlyQuestions mq ON m.Month = mq.Month
LEFT JOIN
  MonthlyAnswers ma ON m.Month = ma.Month
ORDER BY
  mq.Month

fork query

created apr 2 rene