4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @thismonth datetime = dateadd(month, datediff(month, 0, getdate()), 0);

with TopTags as
(
  select top 10 TagId -- select number of tags to return
  from PostTags pt
  join Posts p on p.Id=pt.PostId
  join Tags t on t.Id=pt.TagId
  where t.TagName = 'ocaml' --##CreationDate > getdate()-20 -- most popular tags determined by post count of last x days
  group by TagId
  order by count(*) desc
)
  
select mo, TagName, count(*) as [Count]

from (
select pf.Id, t.TagName,
dateadd(month, datediff(month, 0, min(pf.CreationDate)), 0) mo
from PostFeedback pf
join PostTags pt on pt.PostId=pf.PostId
join TopTags tt on tt.TagId=pt.TagId
join Tags t on t.Id=pt.TagId
where pf.CreationDate between dateadd(month, -96, @thismonth) and @thismonth-1 -- 6 years
group by pf.Id, pf.PostId, t.TagName
) ot
group by mo, TagName
order by mo

view current version

edited feb 24 16

created feb 24 16 SamtheBrand