Number of questions/answers per month of question's CreationDate - 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,
  PCount AS [Total Posts],
  QCount AS [Total Questions],
  ACount AS [Total Answers]  
FROM counts_cte
ORDER BY CMonth

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results