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 - Bad.Closed - Bad.Deleted) desc) as GoodRank,
  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',
  row_number() over (order by (num.Total - Bad.Closed - Bad.Deleted) desc) as Good,
  (num.Total - Bad.Closed - Bad.Deleted) as 'Good 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)

  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