Number of questions/answers/posts per year and a given tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Great Outdoors

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

;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(1000*q.QCount/a.ACount AS Float) AS [Fraction],
  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