Number of questions/answers/posts per month - in a given tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers Meta

Q&A about the site for Drupal developers and administrators

create table #qid(postid int primary key);
insert into #qid
select pt.PostId
FROM PostTags pt 
INNER JOIN Tags t ON pt.TagId = t.Id
WHERE (t.tagname='##tagname?python##')

;with acounts_cte AS (
SELECT
  eomonth(a.CreationDate) AS Cmonth,
  COUNT(DISTINCT a.Id) AS Acount
  FROM Posts a
  INNER JOIN #qid q ON q.PostId = a.ParentId
  GROUP BY eomonth(a.CreationDate)
), qcounts_cte AS (
SELECT
  eomonth(q.CreationDate) AS Cmonth,
  COUNT(DISTINCT q.Id) AS QCount
  FROM Posts q
  INNER JOIN #qid ON PostId = q.Id
  GROUP BY eomonth(q.CreationDate)
)

SELECT
  q.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 to main site
loading Hold tight while we fetch your results