Popular Tags over Time (By Post)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

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(Distinct Posts.Id) as Posts
  From Tags
  INNER JOIN PostTags ON PostTags.TagId = Tags.id
  INNER JOIN Posts ON (Posts.ParentId = PostTags.PostId OR Posts.Id = PostTags.PostId)
  Where Posts.CreationDate >= ##Startdate:string?2019-01-01##
  and Posts.CreationDate <= ##Enddate:string?2019-02-01##
  Group By TagName) as Rate

Inner Join
  (select TagName as Tag, Count(Distinct 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