Top N tags relative popularity history

3

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the most popular tags in percent of all questions asked in the month

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

/*

Parameters:

TopN: how many tags you want to see

TopSince: you want to see the top tags since when

HistorySince: since when you want to see their history

*/

WITH TopTags AS (
  SELECT TOP ##TopN:INT?7##
    Tags.Id,
    MIN(Tags.TagName) AS TagName,
    COUNT(*) AS N
  FROM Tags, PostTags, Posts
  WHERE
    Posts.PostTypeId = 1
    AND
    Posts.Id = PostTags.PostId
    AND
    PostTags.TagId = Tags.Id
    AND
    Posts.CreationDate > ##TopSince:string?2015-01-01##
  GROUP BY Tags.Id
  ORDER BY N DESC
),
PostTagMonth AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
    1) AS Month,
    TopTags.TagName
  FROM Posts, PostTags, TopTags
  WHERE
    TopTags.Id = PostTags.TagId
    AND
    Posts.Id = PostTags.PostId
    AND
    Posts.PostTypeId = 1
),
PostTagMonthGrouped AS (
  SELECT Month, TagName, COUNT(*) AS N
  FROM PostTagMonth
  GROUP BY Month, TagName
),
AllPostMonth AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, CreationDate),
      DATEPART(mm, CreationDate),
    1) AS Month
  FROM Posts
  WHERE PostTypeId = 1
),
AllPostMonthGrouped AS (
  SELECT Month, COUNT(*) AS N
  FROM AllPostMonth
  GROUP BY Month
)
SELECT
  AllPostMonthGrouped.Month,
  PostTagMonthGrouped.TagName,
  100.0 * PostTagMonthGrouped.N / AllPostMonthGrouped.N AS PctOfMonth
FROM AllPostMonthGrouped
LEFT JOIN PostTagMonthGrouped
  ON AllPostMonthGrouped.Month = PostTagMonthGrouped.Month
WHERE AllPostMonthGrouped.Month > ##HistorySince:string?2010-01-01##
ORDER BY AllPostMonthGrouped.Month, TagName

Enter Parameters

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