0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional mathematicians

;with acounts_cte AS (
SELECT 
  eomonth(a.CreationDate) AS Cmonth,
  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 eomonth(a.CreationDate) 
), qcounts_cte AS (
SELECT 
  eomonth(q.CreationDate) AS Cmonth,
  COUNT(DISTINCT q.Id) AS QCount
  FROM Posts q
  WHERE (q.Tags Like '%<##tagname?set-theory##>%')
  GROUP BY eomonth(q.CreationDate)
), counts_cte AS (
SELECT
  q.Cmonth AS CMonth,
  CAST(q.QCount+a.ACount AS Integer) AS PCount,
  q.Qcount AS Qcount,
  a.Acount AS Acount
FROM acounts_cte a INNER JOIN qcounts_cte q ON a.Cmonth=q.Cmonth
) 

SELECT
  Cmonth,
  (CAST(ACount AS decimal))/(CAST(QCount AS decimal)) [Answers to questions ratio]
FROM counts_cte
ORDER BY Cmonth

fork query

created apr 13 Martin20528