Number of questions/answers per month - in two specified tags

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 ((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 ((q.Tags Like '%<##tag1##>%') OR (q.Tags Like '%<##tag2##>%'))
  GROUP BY eomonth(q.CreationDate)
)

SELECT
  q.Cmonth AS CMonth,
  CAST(q.QCount+a.ACount AS Integer) AS [Total posts],
  q.Qcount AS [Total questions],
  a.Acount AS [Total answers]
FROM acounts_cte a INNER JOIN qcounts_cte q ON a.Cmonth=q.Cmonth
ORDER BY q.Cmonth

Enter Parameters

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