Number of questions and answers per year

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

WITH Questions AS (
SELECT YEAR(p.CreationDate) AS [Year], COUNT(*) AS Total
  FROM Posts AS p
  INNER JOIN PostTags AS pt
    ON p.Id = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  GROUP BY YEAR(p.CreationDate)
), Answers AS (
SELECT YEAR(a.CreationDate) AS [Year], COUNT(*) AS Total
  FROM Posts AS a
  INNER JOIN Posts AS p
    ON a.ParentId = p.Id
  INNER JOIN PostTags AS pt
    ON p.Id = pt.PostId
  INNER JOIN Tags AS t
    ON t.Id = pt.TagId
  GROUP BY YEAR(a.CreationDate)
)
SELECT COALESCE(q.Year, a.Year) AS [Year],
  COALESCE(q.Total, 0) AS Questions,
  COALESCE(a.Total, 0) AS Answers
  FROM Questions AS q
  FULL OUTER JOIN Answers AS a
    ON q.Year = a.Year
  ORDER BY 1

Enter Parameters

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