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.Score as 'Average Timeframe Score',
  Num.Score as 'Average Score',
  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, avg(Posts.Score) as Score
  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, avg(Posts.Score) as Score
  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