Tag popularity history

2

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy Meta

Q&A about the site for science fiction and fantasy enthusiasts

DECLARE @TagId INT;
SELECT @TagId = Id
FROM Tags
WHERE TagName=##TagName:string##;

WITH PostStat AS (
  SELECT
    DATEFROMPARTS(
      YEAR(Posts.CreationDate),
      MONTH(Posts.CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM Posts
  WHERE Posts.PostTypeId = 1
  GROUP BY DATEFROMPARTS(
    YEAR(Posts.CreationDate),
    MONTH(Posts.CreationDate),
    1)
),
TagStat AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(mm, Posts.CreationDate),
      1) AS Month,
    COUNT(*) AS Count
  FROM Posts, PostTags
  WHERE
    Posts.PostTypeId = 1
    AND
    PostTags.PostId = Posts.Id
    AND
    PostTags.TagId = @TagId
  GROUP BY DATEFROMPARTS(
    DATEPART(yyyy, Posts.CreationDate),
    DATEPART(mm, Posts.CreationDate),
    1)
)
SELECT
  PostStat.Month,
  100.0 * COALESCE(TagStat.Count, 0) / PostStat.Count AS Pct
FROM PostStat
LEFT JOIN TagStat ON PostStat.Month = TagStat.Month
ORDER BY Month

Enter Parameters

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