Ratio answers/questions posted per month - with some tags excluded

0

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

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 NOT((q.Tags Like '%<##tag1?big-list##>%') OR (q.Tags Like '%<##tag2?soft-question##>%'))
  GROUP BY eomonth(a.CreationDate)
), qcounts_cte AS (
SELECT
  eomonth(q.CreationDate) AS Cmonth,
  COUNT(DISTINCT q.Id) AS QCount
  FROM Posts q
  WHERE NOT((q.Tags Like '%<##tag1##>%') OR (q.Tags Like '%<##tag2##>%'))
  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

Enter Parameters

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