Measuring Tags Over Time

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

WITH Totals AS 
(SELECT YEAR(Posts.CreationDate) AS Year,
       MONTH(Posts.CreationDate) AS Month,
       COUNT(*) AS Total FROM Posts
       WHERE PostTypeId = 1 /* Only questions */
       GROUP BY YEAR(Posts.CreationDate), MONTH(Posts.CreationDate)),
TagTotals AS
(SELECT YEAR(Posts.CreationDate) AS Year,
       MONTH(Posts.CreationDate) AS Month,
       TagName,
       COUNT(*) AS Number
  FROM Posts
  INNER JOIN PostTags On Posts.Id = PostTags.PostId
  INNER JOIN Tags On PostTags.TagId = Tags.Id
  /* Put any tags here that you want to measure */
  WHERE Tags.TagName IN ('gatsby')
  GROUP BY YEAR(Posts.CreationDate), MONTH(Posts.CreationDate), TagName)
SELECT TagTotals.Year,
  TagTotals.Month,
  TagName,
  Number,
  Total,
  (1.0 * Number) / Total AS Percentage
  FROM Totals
  INNER JOIN TagTotals
  ON Totals.Year = TagTotals.Year AND Totals.Month = TagTotals.Month
  ORDER BY TagTotals.Year DESC, TagTotals.Month DESC

Enter Parameters

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