Given tag frequency

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Number of questions about a certain tag in the last year and
-- in total

select 
       num.TagName as Tag,
       rate.Rate as QuestionsLastYear,
       num.Num as QuestionsTotal

from

(select count(PostId) as Rate, TagName
from
  Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId and Tags.TagName='##TagName##'
and Posts.PostTypeId = 1
and Posts.CreationDate < GETDATE()
and Posts.CreationDate > GETDATE() - 365
group by TagName) as rate

INNER JOIN

(select count(PostId) as Num, TagName
from
  Tags, PostTags, Posts
where Tags.Id = PostTags.TagId and Posts.Id = PostId and Tags.TagName='##TagName##'
group by TagName
)
as num ON rate.TagName = num.TagName
order by rate.rate desc
;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results