Popular Tags over Time (By Post)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

Select 
  TagName, 
  row_number() over (order by Rate.Posts desc) as Rank,
  row_number() over (order by num.Total desc) as 'Total Rank',
  Rate.Posts as 'Posts over Timeframe',
  num.Total as 'Total Posts'
From
  (Select TagName, count(p.Id) as Posts
  From Tags
  INNER JOIN PostTags ON PostTags.TagId = Tags.id
  INNER JOIN PostsWithDeleted as p ON (p.ParentId = PostTags.PostId OR p.Id = PostTags.PostId)
  Where p.CreationDate >= ##Startdate:string?2019-01-01##
  and p.CreationDate <= ##Enddate:string?2019-02-01##
  Group By TagName) as Rate

Left Join
  (select TagName as Tag, Count(Posts.Id) as Total
  From Tags
  Inner JOIN PostTags ON PostTags.TagId = Tags.Id
  Inner Join Posts ON (Posts.ParentId = PostTags.PostId OR Posts.Id = PostTags.PostId)
  Group By TagName) as num ON num.Tag = Rate.TagName

Order by Rank

Enter Parameters

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