Number of questions/answers/posts per month - in a given tag

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

;with acounts_cte AS (
SELECT
  year(a.CreationDate) AS Cyear,
  COUNT(DISTINCT a.Id) AS Acount
  FROM Posts q INNER JOIN Posts a ON a.ParentId=q.Id
  WHERE (q.Tags Like '%<##tagname?set-theory##>%')
  GROUP BY year(a.CreationDate)
), qcounts_cte AS (
SELECT
  year(q.CreationDate) AS Cyear,
  COUNT(DISTINCT q.Id) AS QCount
  FROM Posts q
  WHERE (q.Tags Like '%<##tagname?set-theory##>%')
  GROUP BY year(q.CreationDate)
)

SELECT
  q.Cyear,
  CAST(q.QCount/a.ACount*500 AS Float) AS [Total Posts],
  q.Qcount AS [Total Questions],
  a.Acount AS [Total Answers]
FROM acounts_cte a INNER JOIN qcounts_cte q ON a.Cyear=q.Cyear
ORDER BY q.Cyear

Enter Parameters

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