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',
  Bad.Closed as 'Closed Posts',
  Bad.Deleted as 'Deleted Posts'
From
  (Select TagName, count(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

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

Left Join
  (select 
    TagName as Tag, 
    Count(p.DeletionDate) as Deleted,
    Count(p.ClosedDate) as Closed
  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.DeletionDate > '1970-01-01'
  and p.ClosedDate > '1970-01-01'
  Group By TagName) as Bad on Bad.Tag = Rate.TagName

Order by Rank

Enter Parameters

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