1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Joomla! administrators, users, developers and designers

;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

fork query

edited apr 16 Dmitri.Pavlov

created apr 13 Martin20528