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

Q&A about the site for Drupal developers and administrators

/*

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

fork query

edited apr 23 17

created apr 23 17 peterh